Sumif Function

[ml0]

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

[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.
"ml0" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[ml0]

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

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

[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" <[email protected]> wrote in message
news:[email protected]...
>
> 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:H29001))
>
> 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
>

[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 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.

[Alan]

Thats great, I'm glad you have a resolution,
Regards,
Alan.
"ml0" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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


"ml0" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>