

You got an Excel table and want to sum up all numbers in a column – but only, if one or more criteria are fulfilled. This is exactly, what the SUMIFS formula does: The formula adds up all numbers, when one or more than one criteria is fulfilled. The formula only exists since Excel 2007 and is especially useful, as it can regard several search criteria.
Contents
The SUMIFS formula is very versatile and can be used in many cases.
With so many use cases, the SUMIFS formula is one of the most powerful formulas in Excel. So it’s worth spending some time learning how to use it.
SUMIFS has at least 3 arguments
SUMIFS is very straightforward, although the formula can get quite long (the numbers are corresponding to the image on the right hand side):
In the example above, you sum up the prices of all VW Golf in cell F3.
Please note: SUMIFS is quite similar to SUMIF (without “s”). But SUMIF can only regard one criteria, whereas SUMIFS can match up to 127 criteria. So it is recommended using with SUMIFS, even if you only have one criterion.
Example with 2 criteria
The first example above just had one criterion. As a next example, you got two search criteria. The image on the right-hand side shows the example with the following arguments in the SUMIFS formula:
That means, you search for all blue VW Golf cars and sum up their prices. The encircled values in the table fullfil these criteria.
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
The default usage of SUMIFS is to sum up all values if some criteria are true. For example: Sum up all values from column D if there is “VW Golf” written in column B. Fortunately, the SUMIFS formula also works the other way around: Sum up all values if there is not written “VW Golf”. There are three different cases:
Please note: <> stands for “unequal”. It (“<>”) must always be written in quotation marks like in the examples above.
Example: Use SUMIFS horizontally
The SUMIFS formula works also horizontally. Instead of columns, you can define lookup rows and criteria rows. It works exactly the same as vertically. So let’s take a look at an example.
You got monthly data in the columns E to AB. For every month, there are 3 revenue values. You want to summarize them into years.
=SUMIFS($E3:$AB3,$E$1:$AB$1,B$2)
That means:
In conclusion: You sum up the values in E3 to AB3, if in E1 to AB1 is 2014 (the value from B2). You could insert the $-signs so that if you copy the formula, the ranges won’t change.
Instead of a fixed criteria, you can also use “<” and “>”. So if you want to sum up all the values in column B with a search criteria in column A larger than 5, the formula would look like this:
=SUMIFS(B:B,A:A,”>5″)
Classifying data with the SUMIFS formula
You can use this approach for classifying data. You got the following tables:
On the left hand side, there are two columns with cars and their prices. You will use these prices for classifying the cars into Low, Medium and High prices. How do you start?
=SUMIFS(H:H,F:F,”<“&B4,G:G,”>=”&B4)
That means:
3. As the returned value is only a number and you want to get the corresponding name Low, Medium or High, you use a simple VLOOKUP in column D (more information about the VLOOKUP formula). The formula in D4 is:
=VLOOKUP(C4,H:I,2,FALSE)
Step 2 and 3 can be merged into one step. But the structure keeps clearer if they are still separated into 2 steps.
If you don’t want to search for exact matches within your criteria range, you can use wildcard characters. There are two wildcard characters:
An asterisk matches any sequence of characters. For example, you want to search for any string starting with ‘prof’. Then the formula could look like this:
=SUMIFS(H:H,F:F,”prof*”)
It doesn’t matter, how many characters or which characters follow after ‘prof’. Excel will sum up all values in column H for which the value in column F starts with ‘prof’.
The question mark indicates, that one character could vary. Let’s take a look at an example:
=SUMIFS(H:H,F:F,”abc?”)
That means, that you search in column F for a 4 character word, starting with abc. The last character after ‘abc’ can vary
There are some cases in which you might want to search for exactly * or ? . You can use this trick: add the tilde ‘~’ in front of the question mark or asterisk. That way, that wildcard function of the character will be disabled.
Please feel free to download all examples above in one Excel file.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 3
Peg
How does sum if work in conjuction with =right?
When I save a Profit and loss report in pdf format as an excel workbook, it puts words and dollar amounts in one cell. In excel, I use the formula =RIGHT (cell, digits) to copy the last 9 digits (ie the dollar amounts) to the new cell. That way, I have the words (eg Revenue) in first column and dollar amounts (eg $100,000) in the second column. It makes the dollar amounts text, so I have to change the format from text to number.
When I use =SUMIF to sum various items on the P&L (eg travel meals and entertainment meals), sum if doesn’t recognize the dollar amounts as numbers.
Bob
In response to Peg, in case anyone else has a similar issue, your problem is that the dollar amount is still a text string, it is not a number. You need to convert it to a number using VALUE(). So the formula “RIGHT(cell, digits)” should be “VALUE(RIGHT(cell, digits)” .
Henrik Schiffner
Thanks, Bob, for replying!