Sumproduct using multiple colums for criteria and summing 1

[Queryman]

I have my data laid out like this

A	        B        C	        D......
Administrative	100   Group Meetings    200	....
Bereavement	105   Group Meetings    5.....
Group Meetings 	105   Administrative    200.....

I need to use sumproduct to add the values in col B,D,F,H & J like below.

Please note my values to add are in col B, D, F, H & J while the criteria is in col A, C, E, G and I


The formula below does not work, any assistance will be much appreciated. if any other way to do this other than sumproduct exists, please let me know.

SUMPRODUCT(((Jan!A4:A37,Jan!C4:C37,,Jan!E4:E37,,Jan!G4:G37,Jan!I4:I37)='2006 Summary'!A5)*(Jan!B4:B37,Jan!D437,Jan!F4:F37,Jan!H4:H37,Jan!J4:J37))



Michael

 

[bluedragon2]

You need to break out each condition into its own ():

For example:

=SUMPRODUCT((A1:A100="Dog")*(B1:B100="Dog")*(C1:C100)*(d1100))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Queryman]

Thanks Blue, I tried your suggestion, the formula does not give an error, but gives a result of zero. Did you get it to work properly?



Michael

 

[Queryman]

I think I solved it. I need to do the following
SUMPRODUCT((Sheet1!$A$2:$A$11=Sheet1!A15)*(Sheet1!$B$2:$B$11)+(Sheet1!$C$2:$C$11=Sheet1!A15)*(Sheet1!$D$2:$D$11))

So I look at col A and sum the values in B, then to that I add by looking at col C and summing col E etc..




Michael

 

[Lilliabeth]

Maybe this will help you understand how it works.

http://www.dataschenk.com/Newsletters/Tips/ExSumproduct.htm

 

[bluedragon2]

Your second post is differant then your first as for criteria, but takeing your second post you need:

SUMPRODUCT((Sheet1!$A$2:$A$11=Sheet1!A15)*(Sheet1!$B$2:$B$11))+SUMPRODUCT((Sheet1!$C$2:$C$11=Sheet1!A15)*(Sheet1!$D$2:$D$11))

if you want to sum all of those in Col B that match Col A's criteria and Col D that match Col C's criteria.

If you want to only sum Col B and Col D if they match both Col A and C's criteria then you can have:

=SUMPRODUCT((Sheet1!$A$2:$A$11=Sheet1!A15)*(Sheet1!$B$2:$B$11)*(Sheet1!$C$2:$C$11=Sheet1!A15)*(Sheet1!$D$2:$D$11))




[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...