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",P8524)
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
various criteria and a count of unique entries based on criteria.
My current formulas are as follows:
SUM
=SUMIF(V8:V500,"Yes-AFT",P8524)
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