Excel’s SUMIF formulae allows you to obtain the sum of a range of values that meet certain criteria. For example:
If we wanted to obtain the total number of Cars then we could use SUMIF:
=SUMIF(B5:B12,”Cars”,D5:D12)
and if we wanted to obtain the total vehicles in Cambridge we could use:
=SUMIF(A5:A12,”Cambridge”,D5:D12)
However what would we do if we wanted the total number of all the cars in Cambridge ? We can’t use SUMIF as it only allows for one criteria to be selected, and we have two (City and Vehicle Type). The solution is to use SUMPRODUCT:
=SUMPRODUCT(–(Criteria 1),–(Criteria 2), Summation Values)
where Criteria 1 and 2 are the data selection values (i.e City = “Cambridge” and Vehicle Type =”Cars” and the Summation Values are the values that are being summed (Column D – the numbers of vehicles).
=SUMPRODUCT(–($A$5:$A$12=”Cambridge”),–($B$5:$B$12=”Cars”),$D$5:$D$12)
Excel will go through each of the cells in the range A5:A12 and evaluate those that equal “CAMBRIDGE”. For those that do, it assigns the value “TRUE”. For those that do not equal “CAMBRIDGE”, it will assign the value “FALSE”.
The – is a Logical Operator within Excel and is nothing to do with the normal minus operator. This operator converts all “FALSE” values to 0 and “TRUE” values to 1.
This enables us to use SUMPRODUCT in this way to evaluate multiple criteria:
September 11th, 2008 at 1:01 pm
I’ve been using sumproduct for reports that I distribute out to my company for some time. It’s exceedingly powerful since it’s working much like a SELECT query with multiple WHERE statements in the native excel environment (no vba needed). A couple words of caution for those dipping their toes into the ocean of SumProduct. First, it can be wierd with dates. I have found myself formatting a date as a text, and then reformatting it as a date, I don’t know why, but it worked. Second, SumProduct likes to grow with the its referenced ranges in wierd ways. I’ll find (and often) that when I add new data to the range, some components of the sumproduct will capture the new data and some won’t.
=sumproduct((component1)*(component2)*(component3))
Because all components much reference the same number of rows, sumproduct has a bit of a fit when it refigures one range, but not another. Of course, you can get around this by creating dynamic named ranges, but it’s still an oddity of the excel world. Lastly, some formulas don’t play nice when they are embedded in sumproduct. I can’t remember these off of the top of my head, but it’s good to keep in mind that SumProduct is essentially an Array formula, so there’s going to be some formulas that just can’t be a part of your crazy sumproduct schema. And trust me, if you spend a good part of your job in excel, your will eventually end up with some wacki-doo sumproduct formulas.
September 11th, 2008 at 2:43 pm
John,
Thanks. I am well aware of the “issues” that surround SUMPRODUCT.
I personally prefer combining the relevant columns using a helper formulae. If you look on the site, there is an article that does this approach as well.
I essentially did this appraoch for “completeness” to satify the purists.
Thanks for the feedback - it is appreciated !!!
September 15th, 2008 at 10:42 pm
I have what seems to be a unique SUMIF situation - My criteria field needs to match anything that starts with a certain string of text; i.e.
= B18 & “?”. For example, if B18 = “Apples”, it would match “Apples”, “Apples with Cinnamon”, “Apples and Pears”, etc. Any ideas?
September 17th, 2008 at 10:25 pm
I have a spreadsheet that has a tab that represents each day of the month. On each tab it notes the cost of the product, the product brand, and the destination it was shipped to. I want to know the total cost of brand X shipped to destination ABC, and total cost of brand B shipped to destination LMN, and so on for the entire month. Is there a way to have it review a range of tabs? I currently have it set up as follows: =SUMPRODUCT(-(’1st’!N6:N28=”Arctic”),-(’1st’!O6:O28=”Stockton”),’1st’!H6:H28)+SUMPRODUCT(-(’2nd’!N6:N28=”Arctic”),-(’2nd’!O6:O28=”Stockton”),’2nd’!H6:H28)+sumproduct…..this repeats for each tab. Can I simplify it?
September 28th, 2008 at 7:03 pm
I am using a third criteria. I want it to sum if the values are >0 but for some reason it is making the sum a negative number and vice versa. Do you know what I am doing wrong?
October 12th, 2008 at 10:01 pm
If you’re using Excel 2007, you can use the function SUMIFS, which is essentially a SUMIF that allows multiple conditions.