SUMIF adds up the rows that pass a test: =SUMIF(range, criteria, sum_range) tests one column and adds up another. =SUMIF(D2:D7, "North", F2:F7) totals the North’s orders.
B2 = 2100 SUMIF found North on three rows of the Region column and added up their amounts: 1200 + 300 + 600.
Say you have an order log, one row per order, and someone asks how much the North sold. You could filter the list and read the total off the status bar, then do it all again next month. SUMIF answers it with a formula: it tests every row’s region and adds up the amounts of the rows that pass. The same formula totals expenses by category, adds up the hours each person logged, or sums the invoices over a limit.
It comes up often on Excel tests for job applicants, and many of its mistakes are easy to miss: you get a wrong number or a 0, with no error to warn you. Below you build one step by step, then see each argument taken apart, with a small sheet where you can make the classic mistakes yourself.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
SUMIF takes three arguments: the column to test, the test, and the column to add up. It goes down the first column, marks every row that passes, and adds up the third column on those rows only.
B2 = 2100 SUMIF went down the Region column, marked the three rows that say North, and added up the Amount on each of those rows.
Point the test at a cell that holds the region, as the walkthrough does, rather than typing "North" into the formula. Then one formula, filled down, gives every region its line,
and changing a region’s name in the summary changes its total with it. SUMIF works the same way in
Google Sheets. To count the rows instead of adding them up, use COUNTIF.
A test can be a comparison: more than an amount, not a region, before a date. The operator goes in quotes, and the value it compares with is joined on with &. Keep the cell reference outside the quotes, or it stops being a reference.
B2 = 3850 The & joins “>” to the 500 in A2, so the test is >500. Four orders pass. The sum_range is left off because the column tested is the one to add up.
The operators are the ones you’d expect: >, <, >=, <= and <> for “is not”. Text tests ignore upper and lower case,
and * stands for any run of characters, so "Desk*" matches every product
whose name starts with Desk.
A summary has a line per region, so the formula is written once and filled down. Without dollar signs its ranges move with it, one row per line, until the top of the log drops out of reach. The lines further down still find most of their rows, so the totals look plausible.
B4 = 2100 With dollar signs on both ranges, every copy of the formula reads the whole log, and all three North orders count.
You don’t type the dollar signs. Put the cursor on a range in the formula and press F4 (Fn + F4 on most Mac keyboards), once for each range.
SUMIF pairs the column it tests with the column it adds up row by row, counting from the top of each. If one starts a row lower than the other, every row that passes adds up its neighbour’s amount.
B2 = 2100 Both ranges start on row 2, so each North row adds its own amount.
This happens when the ranges are typed rather than selected, or when one includes the header row and the other doesn’t. SUMIFS, below, is stricter: ranges of different sizes give #VALUE! instead of a wrong total.
SUMIF takes one test. For more, such as the North’s desks, use SUMIFS. It takes the column to add up first, then a column and a test for each condition, and adds up only the rows that pass all of them. SUMIF has the column to add up last, so moving between the two from memory is how a formula ends up adding the wrong column.
C2 = 1200 Only rows that pass both tests count: North and Desk. One row does, so SUMIFS adds up its amount.
SUMIFS works with a single test too, so some people use it everywhere and never have to remember which order is which. It’s in every Excel since 2007 and in Google Sheets.
AVERAGEIFS, MAXIFS and MINIFS take SUMIFS’s arguments in SUMIFS’s order. They mark the same rows and
make something different of them. COUNTIFS is SUMIFS without the
column to add up: =COUNTIFS($D$2:$D$7, A2, $E$2:$E$7, "Desk") counts the North’s desk
orders.
B2 = 2100 The three North amounts, added up.
MAXIFS and MINIFS are newer: Excel 2019 and later, Microsoft 365, Excel for the web and Google Sheets. Once SUMIFS reads naturally, so does the rest of the family. Pivot table practice builds the same kind of summary by dragging fields instead.
To carry on in your own Excel, download the workbook: three months of daily website traffic, 450 rows across five channels, and a sheet of tasks. Summarize it by channel with AVERAGEIF and by month with SUMIFS, then go a step further with a conversion rate, the best day and a pivot table.
Three months of daily sessions and signups across five marketing channels. Practice AVERAGEIF and SUMIFS, computed columns like conversion rate, and summarizing by month.
450 rows · Date · Channel · Sessions · Signups
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches SUMIF: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →