software

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: www.excelforum.com/member.php...oamp;userid=31121
View this thread: www.excelforum.com/showthread...hreadid=507874Not with SUMIF, you can with SUMPRODUCT
=SUMPRODUCT(--(A1:A5000=quot;Xtextquot;),--(B1:B5000=quot;Ytextquot;),--(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.
quot;ml0quot; gt; wrote in message
...
gt;
gt; Is there a way to have multiple criteria in a sumif formula? What I need
gt; to do is sum a column if 2 criteria are met. Column A and B are text and
gt; column C is numeric. I want to sum column C if colum A = Xtext and
gt; column B = Ytext.
gt;
gt; Thanks
gt;
gt;
gt; --
gt; ml0
gt; ------------------------------------------------------------------------
gt; ml0's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31121
gt; View this thread: www.excelforum.com/showthread...hreadid=507874
gt;
=SUMPRODUCT(--(A1:A5000=quot;Xtextquot;),--(B1:B5000=quot;Ytextquot;),--(C1:C5000))
=SUMPRODUCT(--(A1:A5000=F1),--(B1:B5000=F2),--(C1:C5000))
Missed the last bracket,
Sorry,
Alan.
quot;Alanquot; gt; wrote in message
...
gt; Not with SUMIF, you can with SUMPRODUCT
gt; =SUMPRODUCT(--(A1:A5000=quot;Xtextquot;),--(B1:B5000=quot;Ytextquot;),--(C1:C5000)
gt; You can have the search criteria in a cell and use that, eg if you put
gt; Xtext in F1 and Ytext in F2,
gt; =SUMPRODUCT(--(A1:A5000=F1),--(B1:B5000=F2),--(C1:C5000)
gt; Regards,
gt; Alan.
gt; quot;ml0quot; gt; wrote in message
gt; ...
gt;gt;
gt;gt; Is there a way to have multiple criteria in a sumif formula? What I need
gt;gt; to do is sum a column if 2 criteria are met. Column A and B are text and
gt;gt; column C is numeric. I want to sum column C if colum A = Xtext and
gt;gt; column B = Ytext.
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt;
gt;gt; --
gt;gt; ml0
gt;gt; ------------------------------------------------------------------------
gt;gt; ml0's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=31121
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=507874
gt;gt;
gt;
gt;

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: www.excelforum.com/member.php...oamp;userid=31121
View this thread: www.excelforum.com/showthread...hreadid=507874Are 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.
quot;ml0quot; gt; wrote in message
...
gt;
gt; Alan,
gt;
gt; Thanks for your posting. I copied your formula and change the cell
gt; reference to fit my spreadsheet and got a value of zero. This is what
gt; my formula looks like:
gt;
gt; =SUMPRODUCT((E1:E29001=V1),(F1:F29001=W1),(H1:H290 01))
gt;
gt; Where V1 and W1 are the text criteria and colum E and F are where the
gt; criteria needs to be tested and column H is the values that I need
gt; added. I tried it by typing in text values instead of the cell
gt; reference and got the same results. Am I doing something wrong?
gt;
gt; Thanks
gt;
gt;
gt; --
gt; ml0
gt; ------------------------------------------------------------------------
gt; ml0's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31121
gt; View this thread: www.excelforum.com/showthread...hreadid=507874
gt;

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: www.excelforum.com/member.php...oamp;userid=31121
View this thread: 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: www.excelforum.com/member.php...oamp;userid=31121
View this thread: www.excelforum.com/showthread...hreadid=507874Thats great, I'm glad you have a resolution,
Regards,
Alan.
quot;ml0quot; gt; wrote in message
...
gt;
gt; Eureka, I got it to work!!! My problem was that the headers were
gt; included in the referenced cells. When I redefined the range to
gt; eliminate the headers it works. So instead of E1:E29000, I changed it
gt; to E2:E29000. It had to be user error.
gt;
gt; Thanks for your help, I have been trying to figure this problem out for
gt; quite a while.
gt;
gt;
gt; --
gt; ml0
gt; ------------------------------------------------------------------------
gt; ml0's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31121
gt; View this thread: www.excelforum.com/showthread...hreadid=507874
gt;
gt; ...the headers were included in the referenced cells...
gt; ...I have been trying to figure this problem out for
gt; quite a while.

If you get stuck like this in the future, you can use Excel's quot;Conditional
Sum Wizardquot;
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. :gt;)

--
HTH. :gt;)
Dana DeLouis
Windows XP, Office 2003quot;ml0quot; gt; wrote in message
...
gt;
gt; Eureka, I got it to work!!! My problem was that the headers were
gt; included in the referenced cells. When I redefined the range to
gt; eliminate the headers it works. So instead of E1:E29000, I changed it
gt; to E2:E29000. It had to be user error.
gt;
gt; Thanks for your help, I have been trying to figure this problem out for
gt; quite a while.
gt;
gt;
gt; --
gt; ml0
gt; ------------------------------------------------------------------------
gt; ml0's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31121
gt; View this thread: www.excelforum.com/showthread...hreadid=507874
gt;