Help With Formulas

[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

[Franz Verga]

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

[IanEmery]

Thanks Franz

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

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

Many thanks

[Domenic]

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 <[email protected]>,
IanEmery <[email protected]>
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

[IanEmery]

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

[Domenic]

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 <[email protected]>,
IanEmery <[email protected]>
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

[IanEmery]

Thanks very much Domenic - you were right all along

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

Thanks again