Help With Formulas
 

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
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=561055

Help With Formulas
 

IanEmery wrote:
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

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

Help With Formulas
 

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!

In article ,
IanEmery
wrote:

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

Help With Formulas
 

Thanks Franz

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

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

Many thanks :)


--
IanEmery
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=561055

Help With Formulas
 

Hi Domenic :)

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

Any ideas :confused:


--
IanEmery
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=561055

Help With Formulas
 

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?

In article ,
IanEmery
wrote:

Hi Domenic :)

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

Any ideas :confused:

Help With Formulas
 

Thanks very much Domenic - you were right all along :cool:

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

Thanks again :)


--
IanEmery
------------------------------------------------------------------------
IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714
View this thread: http://www.excelforum.com/showthread...hreadid=561055