# Sumproduct help

#### Salar

##### Board Regular
=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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Ragnar1211

##### Well-known Member
Try:
Code:
``=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

##### MrExcel MVP
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

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

Cheers

Salar

#### Salar

##### Board Regular
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

##### MrExcel MVP
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")))

Cheers

Salar

Replies
3
Views
280
Replies
1
Views
169
Replies
4
Views
298
Replies
0
Views
95
Replies
15
Views
505

1,191,719
Messages
5,988,284
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back