sum if with multiple criteria

[ptrifile]

I have a spreadsheet where i am trying to sum cells if 2 other cells match certain criteria...i attempted using the sumif statement but found that will only except single criteria. A sample of my data is below:

State Mileage Loaded/unloaded
NY 100 loaded
PA 150 loaded
NY 200 unloaded
PA 150 loaded
NY 50 loaded

I am trying to sum the mileage for each state but only sum it if the "loaded/unloaded" = "Loaded

so for example if i wanted to sum the mileage for new york the total would be "150".

Any help is greatly appreciated!

Thanks in advance

Paul

 

[AnotherHiggins]

If you're using Excel 2007 or 2010, then just use SumIfs, which allows for multiple criteria.

If you're on an earlier version, you can use SumProduct. For your example, it would look like this:

[tab][COLOR=blue white]=SumProduct( --(A2:A6 = "NY") * --(C2:C6 = "Loaded") * --(B2:B6) )[/color]

Bear in mind that you can use cell references instead of hard coding the criteria. That might look like this:
[tab]=SUMPRODUCT( --(A2:A6 = E1) * --(C2:C6 = F1) * --(B2:B6) )

A couple of notes on SumProduct:
- You can't use a reference to an entire column (so "A:A" is out)
- The number or rows in each range must be the same

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fishymetrics]

if you use the Conditional Sum Wizard Add-in it makes the formula for you. You can have multiple criteria.
go to

http://support.microsoft.com/kb/214249

for details.

For your data it created
{=SUM(IF($C$2:$C$6="loaded",IF($A$2:$A$6="NY   ",$B$2:$B$6,0),0))}

The {} brackets indicate an array
You can enter an array by using ctrl+shift+enter
Without the array the formula does not work.

C

 

[AnotherHiggins]

fishymetrics:

SumProduct is functioning as an array formula, but because it's built in to Excel you don't have to enter with [Ctrl]+[Shift]+[Enter] (so it doesn't have the curly brackets around it). Either solution is basically working the same way - by looping through each row in each range to see where conditions are met.

But I think it's worth noting that the user has to enable an extra add-in before they can even see the Conditional Sum Wizard. It is not loaded in Excel by default.

So we've offered three solutions; there are many ways to skin a cat.

(But I think that SumIfs is clearly the best solution for an Excel novice if they have 2007 or later.)

So, ptrifile, what version of Excel are you running? Did you get this sorted out?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fishymetrics]

I have 2003 which doesn't have SUMIFS. Conditional Sum Wiz is the "SUMIFS" of 2003.
I offered it as a solution because the wizard steps thru the process and puts all of the IFs, ranges, operators, commas, and parens in the right place for up to 7 criteria. Once you load it, its there anytime you need it.

C

 

[AnotherHiggins]

Conditional Sum Wiz is the "SUMIFS" of 2003.

That's an overstatement. Many folks, including most (if not all) frequent contributors here at Tek-Tips, consider SumProduct "the 'SumIfs' of 2003".

No addin required. True, it doesn't have a wizard*. But I personally hate wizards, so that doesn't bother me.

I see you're new here. Please understand that I'm not trying to diminish your contribution. You offered another way to get the same results, and there's nothing wrong with that. More information is great. My point is that my initial reply did specifically offer a solution that works in 2003 and previous versions.

Welcome to Tek-Tips and thanks for being willing to donate your time to help others. That's a rare trait that is very much appreciated around here. So, again, cheers.



*Actually SumProduct does have a wizard, as does any formula in Excel. But because hit is an "off label" use of SumProduct, the wizard might not help a novice very much.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wec43wec]

What does the "- -" mean in the formula?