Sumif with multiple conditions [quick tip]

Could you not just use the sumproduct function in the exact same way, yet with more flexibilty as you are avoiding using it as an array?

ie ( grain of salt on table location, please ) =SUMPRODUCT(($A$1:$A$7=$F3)*($B$1:$B$7=G$1)*($C$1:$C$7))

I would normally do this with SUMPRODUCT

Is there any advantage to using arrays?

Hi, I am fairly new to this site and I really thank Chandoo for maintaining this site, but trust me on Sum Product, It is THE best formula that I have ever come across.

For Eg you can replace a PIVOT table using sum product. Yeah I see a lot of frowns, but SUM Product equally works on numbers as well as on text.

For Text, use =sumproduct(–(Range1=”abc”),–(Range2=”XYZ”)) where range1 is the range and abc is the condition in that range
In this, excel would return an array in the 1st range(1s and 0s for matching abc in the Range1), and return an array in the second range(1s and 0s for matching XYZ in the Range2), and voila, you have the data. Just make sure that the start and end points of Range1 and Range2 are same. (Length of both the range should be exactly same or else you get a #VALUE error.

As for Numbers, just remove the 2 hyphens before opening the brackets and remove the condition…. ! and additionally, you can mix text and numbers and remove hyphens for any ranges having only numbers(it would return the count of occurence)

Hope you got the point….. I am all for Sum product..

One word of caution though..Since this returns and array for every condition, if might get heavy on the excelsheet.

Hi Chandoo,

In Excel 2007 you can also use the new SUMIFS function to sum with multiple conditions. The only rub is that it isn’t backwards compatible like the array or SUMPRODUCT solutions.

You guys are fantastic !!! I’m finding easier each time to work with named ranges !
Here’s my little contribution:
Named Ranges (should be dynamic, but….)
Ship $A$2:$A$8
Captain $B$2:$B$8
flights $C$2:$C$8
in F:F
Summary_ship $F$2:$I$2
this 3:3
Summary_Captain $E$3:$E$6

=SUMPRODUCT((Ship=in Summary_ship)*(Captain=this Summary_Captain)*(flights))

data is in range A1:C8, and summary is in E1:I6.

rgds,

Martín

I like that the 8th starship flight on your list has been pixellated. Is this the secret black-ops flight to Titan, and did the Intergalactic Intelligence Agency pressure you to keep that info under wraps?

Kpil, would you explain what the hyphens are for please? Or is it only to indicate text rather then numbers?
Thank you.

Kapil, would you explain what the hyphens are for please? Or is it only to indicate text rather then numbers?
Thank you.

Cheryl, the output of an expression of the form X=Y, in Excel, is one of the logical values TRUE or FALSE. These can often be evaluated as 1 or 0, but SUMPRODUCT needs a little clue first. Typing in two negative signs provides the clue by turning the logcal values into numerical values (negating and then negating again leaves the values unchanged, but now they’re obviously numbers and not Boolean entities).

There is an alternative to two minus signs, and that’s the N() function, which evaluates anything inside the brackets as a number. But there’s no obvious advantage to using N(), and most Excel users don’t bother.

Cheryl : the 2 hypens make sumproduct return an array, and multiple conditions then have multiple arrays…

Once you have the result in arrays then a sumif is performed internally and result is displayed..

I hope this helps…

Rather put it simply, it converts the SUMPRODUCT into an array formula (something similar to CTRL+SHIFT+ENTER thing that many ppl do to convert a formula into array formula… )

For More on Arrays, Chandoo and his wonderful site can explain in a much subtle and simpler way

Derek: I am sorry, but I did not understand your post. When the two hypens are used, it ask sumproduct to return the output in the form of an array (something similar to CTRL+SHIFT+ENTER). Please correct me if I am wrong, since I use this day in and day out but i never encountered a problem..

Derek was correct.

The two ‘hyphens’ are simply two negative symbols, turning TRUE or FALSE into 1 or 0, and using the * between the arrays in Simon’s example does the same thing.

I use the — method because it looks clearer to me, but both work.

My SUMPRODUCT formulas end up like :

=SUMPRODUCT(–(Section=”Management”),–(Salary=”Too High”))

the other way is

=SUMPRODUCT((Section=”Management”)*(Salary=”Too High”))

horses for course really…

silly formatting changed two – into –
should be – -

Thanks! very very helpful :0)

i’m sorry if this is a stupid question, but why do you use ‘$’ in the function? Isn’t ($D$3:$D$24) the same thing as writing (D3:D24) meaning the “range of values from D3 block to D24 block”? Is it because you are working with arrays?

The $ makes an absolute reference that doesn’t change if you copy the formula to another cell.

If you copy a formula with ($D$3:$D$24) from A1 to B1 it stays the same, but if you copy a formula with (D3:D24) it becomes (E3:E24) – this is probably not going to be what you want – although it might be – depends on circumstances (as always … )

This is why using named ranges can be useful as it makes it clearer what is being done.

(IMHO of course…)

I am using arrays in a spreadsheet ,but have found them to be very slow. Will my problems be solved if I replace them with SUMPRODUCT?

oh! thanks! I learned something today!

Thanks Chandoo – you have a great site !

Very helpful?
Please solve the following situation in Excel:
A2:A10 is different fruits name
B2:B10 contains either day or night
C2:C10 contains contents in number
Now,
Search “Apple” from A2:A10,
search “Day” from B2:B10 and
sum all the numbers (c2:c10)meeting the above criteria

Indeed. Perfect case for SUMPRODUCT

=SUMPRODUCT(–(A2:A10=”Apple”),–(B2:B10=”Day”),C2:C10)
or
=SUMPRODUCT((A2:A10=”Apple”)*(B2:B10=”Day”)*(C2:C10))

Using named ranges would make it even clearer…

I am trying to put together a tool that can can search, column by column,
for a certain background/fill color, using Conditional Formating (Red), and then copy/paste the
value/text in various columns of the same row for each instance of Red
background colored cells, into another worksheet.

Thanks, any help would be greatly appreciated…