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

Thanks


--
ml0
------------------------------------------------------------------------
ml0's Profile: http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

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.
"ml0" wrote in message
...

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

Thanks


--
ml0
------------------------------------------------------------------------
ml0's Profile:
http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

=SUMPRODUCT(--(A1:A5000="Xtext"),--(B1:B5000="Ytext"),--(C1:C5000))
=SUMPRODUCT(--(A1:A5000=F1),--(B1:B5000=F2),--(C1:C5000))
Missed the last bracket,
Sorry,
Alan.
"Alan" wrote in message
...
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.
"ml0" wrote in message
...

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

Thanks


--
ml0
------------------------------------------------------------------------
ml0's Profile:
http://www.excelforum.com/member.php...o&userid=31121
View this thread:
http://www.excelforum.com/showthread...hreadid=507874

Alan,

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

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

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

Thanks


--
ml0
------------------------------------------------------------------------
ml0's Profile: http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

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

Alan,

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

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

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

Thanks


--
ml0
------------------------------------------------------------------------
ml0's Profile:
http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

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
------------------------------------------------------------------------
ml0's Profile: http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

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

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


--
ml0
------------------------------------------------------------------------
ml0's Profile: http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

Thats great, I'm glad you have a resolution,
Regards,
Alan.
"ml0" wrote in message
...

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

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


--
ml0
------------------------------------------------------------------------
ml0's Profile:
http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874

...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. :)

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"ml0" wrote in message
...

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

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


--
ml0
------------------------------------------------------------------------
ml0's Profile:
http://www.excelforum.com/member.php...o&userid=31121
View this thread: http://www.excelforum.com/showthread...hreadid=507874