Sumproduct help

[Salar]

=IF(TODAY()<=DATE(2009,6,30),0,SUMPRODUCT(--('R3 2009 EC''s'!$C$3:$C$854="CAT3,"CAT4"),--('R3 2009 EC''s'!$AL$3:$AL$854<>"EC Approved")))

I am using the above formula to count overdue categories after 30 June '09. whe I have CAT1 and CAT2 as single items the formula works ok. But when I try to combine CAT3 & CAT4 I get an error.

Any pointers would be appreciated.

thanks

Salar

 

[Ragnar1211]

Try:

=IF(TODAY()<=DATE(2009,6,30),0,SUMPRODUCT(--('R3 2009 EC''s'!$C$3:$C$854="CAT3"),--('R3 2009 EC''s'!$AL$3:$AL$854<>"EC Approved"))+SUMPRODUCT(--('R3 2009 EC''s'!$C$3:$C$854="CAT4"),--('R3 2009 EC''s'!$AL$3:$AL$854<>"EC Approved")))

 

[barry houdini]

Try like this

=IF(TODAY()<=DATE(2009,6,30),0,SUMPRODUCT(--ISNUMBER(MATCH('R3 2009 EC''s'!$C$3:$C$854,{"CAT3","CAT4"},0)),--('R3 2009 EC''s'!$AL$3:$AL$854<>"EC Approved")))

 

[Salar]

Thanks for your help. this appears to work when I change the expiry date within the formula.


Cheers

Salar

 

[Salar]

Try like this

=IF(TODAY()<=DATE(2009,6,30),0,SUMPRODUCT(--ISNUMBER(MATCH('R3 2009 EC''s'!$C$3:$C$854,{"CAT3","CAT4"},0)),--('R3 2009 EC''s'!$AL$3:$AL$854<>"EC Approved")))

Yes, this works too. I understand how the first version work in my laymans eyes.

Thank you too for your help.


Salar

 

[barry houdini]

Yes, this works too. I understand how the first version work in my laymans eyes.

There's no reason why you can't use 2 SUMPRODUCT formulas here but should you want to check several categories the ISNUMBER(MATCH approach would be a lot simpler.

I don't really recommend it but you can also use this version:

=IF(TODAY()<=DATE(2009,6,30),0,SUMPRODUCT(('R3 2009 EC''s'!$C$3:$C$854={"CAT3","CAT4"})*('R3 2009 EC''s'!$AL$3:$AL$854<>"EC Approved")))

 

[Salar]

thanks again for your time


Cheers

Salar