The Excel SUMIF function adds up only the numbers you choose, not everything in a range. Plain SUM() totals every cell with no filter, which is fine until you need the sales for one person, one month or one product. SUMIF fixes that by letting you set a condition first, then adding only the matching values. The one catch that trips people up is the argument order, because SUMIF puts the filter before the numbers to total. Here is how it works, with a clear example you can copy.
SumIF sometimes confuses people because they, understandably, expect the range to be SUMmed to come first followed by the condition. After all, most functions based on others start with the same parameter order as its ancestors then extend the features for more options at the end.
SumIF, conditions then values
With SumIF the conditions come first and the values to be added up are last.
SUMIF(<range of filter criteria>, <criteria to search for>, <range to sum>)
Range of filter criteria . It’s the range of cells that you want filtered. The range can be numbers, names, arrays or dates. Microsoft calls this just ‘range’ which just adds to the confusion.
Criteria to search for. This can be a single value to match e.g. “64” “Dopey” “Tuesday” or a cell reference to a value. Or a short IF filter like <64 for values below 64.
Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not required.
You can use the wildcard characters—the question mark (?) and asterisk (*)—as the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
sum_range The actual cells to add up after filtering. If you want to SUM the same range as you’re filtering (e.g. Sales over 1,000) then this parameter isn’t needed. Excel will assume you mean to filter and SUM the same range.
Very commonly, the search range is different from the SUM range, most likely matching columns of a list(e.g. Sales for a single name). That’s when you add the third parameter to tell which range to add up.
SumIF in action
A simple example of SumIF at work with a simple table. Adding up the values for each dwarf is a perfect job for SumIF.
Each of the Totals (right) are made with the formula:
=SUMIF($B$2:$B$14,E2,$C$2:$C$14)
Here’s how it’s done with =SUMIF(Dwarves[Name],E2,Dwarves[Value]).
We’ve made a separate table with the names of the short seven in one column. The neighboring column has the SumIF:
Search Range is Dwarves[Name], the list of names in the original table.
Criteria the cell immediately to its left, with the current dwarf’s name.
Sum Range is the list of values in the original table.
In this example, we’ve typed the list of individual dwarfs but in the real world it’s faster and more adaptable to make the unique list based on the original.
SUMIFS Excel SUM with filters galore
Eight SUM functions in Excel … and more
Faster SumIFS in Excel 365 for Windows
Three ways to make an auto-update unique list in Excel
IF and Nested IF Statements in Excel
Excel – Nested IFs and alternatives
Live Sorting & Filtering in Excel: How to Use FILTER() + SORT() for Dynamic Lists
