Help With Formulas

  • Thread starter Thread starter IanEmery
  • Start date Start date
I

IanEmery

I have a spreadsheet and I need a formul to calculate a sum based on
various criteria and a count of unique entries based on criteria.

My current formulas are as follows:

SUM

=SUMIF(V8:V500,"Yes-AFT",P8:P524)

COUNT

{=SUM(IF(FREQUENCY(IF((A8:A500<>"")*(V8:V500="Yes-AFT"),MATCH(A8:A500,A8:A500,0)),ROW(INDIRECT("1:"&ROWS(A8:A500))))>0,1))}

I need to adapt these to take into account another condition, i.e.
where the derived quarter in range N8:N524 is equal to that shown in a
control field in cell J2

The quaters are currently derived from date entry using the following
formula:

=IF(M8 > 0,"Q"&INT(1+MOD(MONTH(M8)-4,12)/3)&"
"&YEAR(M8)-1+(MONTH(M8)>=4),"")

Any help would be greatly appreciated as this is driving me crazy

Many thanks
 
IanEmery said:
I have a spreadsheet and I need a formul to calculate a sum based on
various criteria and a count of unique entries based on criteria.

My current formulas are as follows:

SUM

=SUMIF(V8:V500,"Yes-AFT",P8:P524)

COUNT

{=SUM(IF(FREQUENCY(IF((A8:A500<>"")*(V8:V500="Yes-AFT"),MATCH(A8:A500,A8:A500,0)),ROW(INDIRECT("1:"&ROWS(A8:A500))))>0,1))}

I need to adapt these to take into account another condition, i.e.
where the derived quarter in range N8:N524 is equal to that shown in a
control field in cell J2

The quaters are currently derived from date entry using the following
formula:

=IF(M8 > 0,"Q"&INT(1+MOD(MONTH(M8)-4,12)/3)&"
"&YEAR(M8)-1+(MONTH(M8)>=4),"")

Any help would be greatly appreciated as this is driving me crazy

Many thanks
Click to expand...

I think you could use a SUMPRODUCT formula or a INDEX MATCH (array entered)
formula. If you need more help you could upload an example file to
www.savefile.com


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Try...

=SUMPRODUCT(--(N8:N524=J2),--(V8:V524="Yes-AFT"),P8:P524)

and

=SUM(IF(FREQUENCY(IF(A8:A524<>"",IF(N8:N524=J2,IF(V8:V524="Yes-AFT",MATCH
(A8:A524,A8:A524,0)))),ROW(A8:A524)-ROW(A8)+1),1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Thanks Franz

Unfortunately I am unable to uploads a file due to network restriction
(:mad:)

Would it be possible to provide an eample of your ideas.

Many thanks :
 
Hi Domenic :)

Thanks for your reply - your first formula works beautifully. However
receive a standard MS Excel "error in formula" dialogue box whe
committing the second formula using Ctrl/Shift/Enter.

Any ideas :confused
 
If you copied and pasted the formula from the post into your worksheet,
make sure hard returns haven't been added to the formula...

Is this the case?
 
Thanks very much Domenic - you were right all along :cool:

Embaressed that I did something so naive :eek: Should never hav
doubted you.

Thanks again :
 
Back
Top