SUMIF adds up the rows of a list that pass a test, and COUNTIF counts them. Together they turn a long list into a summary that keeps itself up to date.
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. COUNTIF does the same test and counts the rows instead. The same pair totals expenses by category, counts the tickets still open, or adds up the hours each person logged.
Both are staples of Excel tests for job applicants, and both fail quietly: a mistake gives a wrong number or a 0, never an error. Below you build them 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.
COUNTIF takes SUMIF’s first two arguments and stops there: it answers how many rows pass, not what they add up to. The test works the same way in both, so a comparison you can write for one, you can write for the other.
B2 = 3 Three rows of the Region column say North, so COUNTIF answers 3. It counts rows; it doesn’t look at the amounts.
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 = 3 With dollar signs on both ends, every copy of the formula tests D2:D7, and all three North rows count.
SUMIF’s two ranges slide the same way, so lock both: $D$2:$D$7. You don’t type the dollar
signs. Put the cursor on the range in the formula and press F4 (Fn + F4 on most Mac keyboards).
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.
For more than one test, 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. Summarise 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 & COUNTIF: 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 →