Sumif Function

[ml0]

Is there a way to have multiple criteria in a sumif formula? What I nee
to do is sum a column if 2 criteria are met. Column A and B are text an
column C is numeric. I want to sum column C if colum A = Xtext an
column B = Ytext.

Thank

 

[Alan]

Not with SUMIF, you can with SUMPRODUCT
=SUMPRODUCT(--(A1:A5000="Xtext"),--(B1:B5000="Ytext"),--(C1:C5000)
You can have the search criteria in a cell and use that, eg if you put Xtext
in F1 and Ytext in F2,
=SUMPRODUCT(--(A1:A5000=F1),--(B1:B5000=F2),--(C1:C5000)
Regards,
Alan.

 

[Alan]

=SUMPRODUCT(--(A1:A5000="Xtext"),--(B1:B5000="Ytext"),--(C1:C5000))
=SUMPRODUCT(--(A1:A5000=F1),--(B1:B5000=F2),--(C1:C5000))
Missed the last bracket,
Sorry,
Alan.

 

[ml0]

Alan,

Thanks for your posting. I copied your formula and change the cel
reference to fit my spreadsheet and got a value of zero. This is wha
my formula looks like:

=SUMPRODUCT((E1:E29001=V1),(F1:F29001=W1),(H1:H29001))

Where V1 and W1 are the text criteria and colum E and F are where th
criteria needs to be tested and column H is the values that I nee
added. I tried it by typing in text values instead of the cel
reference and got the same results. Am I doing something wrong?

Thank

 

[Alan]

Are you using:
=SUMPRODUCT((E1:E29001=V1),(F1:F29001=W1),(H1:H29001))
It should be:
=SUMPRODUCT(--(E1:E29001=V1),--(F1:F29001=W1),--(H1:H29001))
note the '-' signs, they are very important,
Regards,
Alan.

 

[ml0]

I actually did it both ways originally. I thought that the '--' were
strange so I left them out. The results were 0. I then did it with the
'--' and the results were #value!. Here is what I have input in fact I
just copied it from your post:

=SUMPRODUCT(--(E1:E29001=V1),--(F1:F29001=W1),--(H1:H29001))

I also tried the same formula but instead of cell references I used
text to replace V1 and W1. Same result. I then tried using E:E and F:F
and H:H instead of using the row numbers. Again, same results. Could
the version of Excel make a difference, I have 2003?

Thanks again for your assistance.

 

[ml0]

Eureka, I got it to work!!! My problem was that the headers wer
included in the referenced cells. When I redefined the range t
eliminate the headers it works. So instead of E1:E29000, I changed i
to E2:E29000. It had to be user error.

Thanks for your help, I have been trying to figure this problem out fo
quite a while

 

[Alan]

Thats great, I'm glad you have a resolution,
Regards,
Alan.

 

[Dana DeLouis]

...the headers were included in the referenced cells...

...I have been trying to figure this problem out for
quite a while.

If you get stuck like this in the future, you can use Excel's "Conditional
Sum Wizard"
Go to Tools | Add-Ins... and add the Wizard.
Now, select your table (including headers) and do Tools | Conditional Sum...
You will notice that Excel prefers to use an Array formula.
Just thought I'd mention it for future reference. :>)