SUMIF – Multiple Criteria
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:
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.
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 !!!
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?
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?
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?
If you’re using Excel 2007, you can use the function SUMIFS, which is essentially a SUMIF that allows multiple conditions.
I have developed a questionnaire which requires an yes or no answer with an x. This questionnaire will continually be added to. I am trying to put in a formula to add all the no and all the yes on individual rows. Can you use a SUMPRODUCT formula which will grow with the spreadsheet?
I really am a newbie at this and would greatly appreciate any help. Thank you.
It would seem that all you need to do is create dynamic named range for the product data base area. Ratehr than offer a solution here I suggets you look up the use of “offset” try http://www.ozgrid.com/Excel/DynamicRanges.htm
I am sure this will help
Hi,
I too found sumproduct formula very interesting and usefull.
Thank you All.
Hi, This formula never works for me. I reproduced your excel sheet, and copied the formula, it does not work. Han anyone else tried?
L have an easiest method about sumif with multiple criteria. You cannt use SUMPRODUCT in one Excel.xls 1000 time. For its you need 15 minutes.
But l have a best suggest which you can use more criterial sumif 100.000.000 time calculation during 5 secund. Its revolution.
Look at carefully !
Set your columns . Example
1)A1- Dealers.(A2:A10)
2)B1- Countries.(B2:B10)
3)C1- Profits.(C2:C10)
You have some dealers in some countries, and they have different profit. And you want to sum PROFİT— DEALER by name “HONDA” and situated in COUNTRY “USA”.
Step by Step
1)Add a new fourth column by name D1- Dealer&Country.(D2:D10)
2)Add to every cell in this column function CONCOTANATE
=A2&B2 , =A3&B3 …=A10&B10
3)And finally this function for you. You can use everywhere.Example you want to find Dealers by name Honda (A2),(A5),(A7),(A9) situated in County USA(B5)(B7).
=SUMIF(D2:D10;A2&B7;C2:C10)
In this function u can use your every criterias concocoating.
Enjoyyyyyyyyyy
Sahil Huseynov from Azerbaijan.
Sahil, I cannot thank you enough!
I worked a long time today trying to get “Sumifs” and “Sumproducts” to work for my problem of having too many rows in the sum range.
Anyway, I think this simple solution will work for many of my work applications.
Have a great night!
And if you want to use third or other criterias you can add the fourth column formulas a new element. Example =SUMIf(D2:D10;A2&B7&F5;C2:C10)
And my suggest work better than Excel 2007 SUMIFS
I have tried to use sumif with two conditions and finally find you, the answer. Thanks a lot. nh
It’s a great site, & it is very helpful & also increased to efficiency,
Sahil offers a great alternative. I have used helper columns with excellent results.
CONCATENATE works well with simple matching criteria but…
What about conditions that involve logical operators (greater than, less than, etc)?
Use helper columns with Logical functions (AND, IF, etc).
Here is a SUMIF Multiple example; it uses helper columns, Excel Logical functions and also CONCATENATE: http://www.excel-spreadsheet-authors.com/sumif-multiple.html I hope it helps
Hi, I want to know if i have 2 columns say “C – Amount” & “D – Amount” and i want to use the differnce between these 2 columns to be added with a criteria given in column B which contains Names. Can i use sumif ?
when i tried doing this it gave the result as number of rows which this crieteria is satisfied… am i doing anything wrong? Answer would be highly appreciated. Thanks
I’m new to excel and i need help. I need a formula that will get a sum total from a collum on a general ledger page (collum E), but it mus meet two diffrent criterias: First it must have been entered in a certain month (collum 1), and second it must have a certain text code (collum 3).
The sum total needs to displayed in a cell on a diffrent page (monthly statement page).
Thank You
I know this should be a simple formula, but I can’t seem to find the right combination.
A1 B1 C1
39
I know this should be a simple formula, but I can’t seem to get the right combination.
A1 B1 C1
40 39
If B1A1 then C1 = B1-A1
Sir
My name is jimmy i am a computer operator i have a very big problem in sumif function i can’t calculate multi criteria function plz help me
My question is : (arrey) 1500,3600,3500,1800,500 how to i calculate total more then or equal 500 hundred and less than or equal to 2000
Please Suggest me and give example
I am trying to figure out how many items I need to order for every item listed. I have a sheet of items (rows) where for each row there may be an amount needed or not. For each row that has a quantity needed, I need to create another sheet with ONLY the rows where there is an actual requirement with the quantity needed, item name and state. Here is sample data of the inventory in Worksheet A:
Name State Qty
Apples WA 5
Oranges FL
Car MI 8
Furniture VA 3
Corn IN
Notice that the Qty is null in many of the rows. Desired output on Worksheet B:
Name State Qty
Apples WA 5
Car MI 8
Furniture VA 3
So the output does not include the empty qty records. I’ve tried various vlookup formulas and others and am just having a hard time. I’d prefer to do this with a formula rather than VBA macro or such and not using a filter. Any help would be appreciated.
Anyone want to tackle this?
Consider the following table
Name State Qty
Apples Ripe 3
Mangoes Raw 2
Apples Rotten 5
Mangoes Ripe 7
Apples Raw 1
Mangoes Rotten 5
Grapes Ripe 7
Bananas Raw 9
How can i get the number of all Apples and Mangoes that ARE NOT rotten?