sumproduct or statement

[blossomthe2nd]

Hi Guys

Im puling data from another spreadsheet that have the below columns

Date Area Type Product

I have a sumproduct that basically counts when the month of the date is equal to "Jan" and Area is equal to "Clothing" and Type is equal to "Summer" it looks a bit like the following

=SUMPRODUCT(--(MONTH('[Customer 2012.xls]Customer'!$A$7:$A$31)=1),--('[Customer 2012.xls]Customer'!$B$7:$B$31="Clothing"),--('[Customer 2012.xls]Customer'!$C$7:$C$31="Summer"))

It works fine

But now I have to count when area is equal to either "Clothing" or "Groceries" or "Hardware"
I have amended formula to

=sum((SUMPRODUCT(--(MONTH('[Customer 2012.xls]Customer'!$A$7:$A$31)=1),--('[Customer 2012.xls]Customer'!$B$7:$B$31="Clothing"),--('[Customer 2012.xls]Customer'!$C$7:$C$31="Summer"))),(SUMPRODUCT(--(MONTH('[Customer 2012.xls]Customer'!$A$7:$A$31)=1),--('[Customer 2012.xls]Customer'!$B$7:$B$31="Groceries"),--('[Customer 2012.xls]Customer'!$C$7:$C$31="Summer"))),(SUMPRODUCT(--(MONTH('[Customer 2012.xls]Customer'!$A$7:$A$31)=1),--('[Customer 2012.xls]Customer'!$B$7:$B$31="Hardware"),--('[Customer 2012.xls]Customer'!$C$7:$C$31="Summer")))

And although this works fine I anticipate my Boss adding more and more criteria and eventually I will get the "Formula too long error" -

Is it possible to have an OR Criteria as part of the sumproduct ??

Any suggestions greatly appreciated

Thanks

A

 

[VoG]

Try

=SUMPRODUCT(--(MONTH('[Customer 2012.xls]Customer'!$A$7:$A$31)=1),--ISNUMBER(MATCH('[Customer 2012.xls]Customer'!$B$7:$B$31,{"Clothing","Groceries","Hardware"},0)),--('[Customer 2012.xls]Customer'!$C$7:$C$31="Summer"))

 

[blossomthe2nd]

Hi VoG

for the part thats ),--ISNUMBER(MATCH('[Customer 2012.xls]Customer'!$B$7:$B$31,{"Clothing","Groceries","Hardware"},0)),

Is it possible to use cell references instead of "clothing" "groceries" etc ?

Thanks

A

 

[VoG]

Not as far as I know. To use cell addresses you'd need something like the formula that you posted.

 

[blossomthe2nd]

Well now thats a bummer !!!!

Thanks for your help