COUNTIF counts the cells that pass a test: =COUNTIF(range, criteria) takes the column to test and what to test for. =COUNTIF(D2:D7, "Open") counts the open tickets.
B2 = 3 COUNTIF went down the Status column and found Open on three rows.
Say you have a log of support tickets, one row per ticket, and someone asks how many are still open. You could filter the list and read the count off the status bar, then do it all again tomorrow. COUNTIF answers it with a formula: it tests every row’s status and counts the ones that pass. The same formula counts the invoices over a limit, the people who said yes, or the orders from one region.
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 kind of test 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.
COUNTIF takes two arguments: the column to test, and the test. It goes down the column and counts every cell that passes.
B2 = 3 Three rows of the Status column match the Open in A2. COUNTIF counts rows; it doesn’t look at any other column.
Point the test at a cell that holds the value, as the walkthrough does, rather than typing "Open" into the formula. Then one formula, filled down, gives every status its line.
COUNTIF works the same way in Google Sheets. To add up another column on those rows instead of
counting them, use SUMIF.
A test on text looks at the whole cell, so "printer" doesn’t find “Printer jam”. To find
a word anywhere in the cell, put an asterisk on each side of it. The asterisk stands for any run of
characters, including none.
B2 = 3 An asterisk on each side finds the word anywhere in the cell, in capitals or not.
A question mark stands for one character, so "T-???" matches any code of a T, a hyphen
and three more characters. To look for a real asterisk or question mark, put a tilde in front of
it: "~*".
Three short tests answer three questions about a column that isn’t full: which cells hold text, which hold anything, and which hold nothing.
B2 = 3 A lone asterisk passes any text, so the three names count. The 4017 is a number, so it doesn’t, and neither do the blanks.
The difference between the first two is numbers: "*" skips them and "<>" counts them. Excel also has COUNTBLANK, which gives the same answer as "" and is easier to read.
A test can be a comparison: more than a number, not a status, 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 = 2 The & joins “>” to the 5 in A2, so the test is >5. Two tickets pass, and the one on exactly 5 days doesn’t.
The operators are the ones you’d expect: >, <, >=, <= and <> for “is not”. To count the values between two limits,
use COUNTIFS, below, with one test for each limit.
A summary has a line per status, so the formula is written once and filled down. Without dollar signs its range moves 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 counts look plausible.
B4 = 3 With dollar signs on both ends, every copy of the formula tests D2:D7, and all three Open rows count.
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).
COUNTIF takes one test. For more, such as the open tickets older than 4 days, use COUNTIFS. It takes a column and a test for each condition, and counts only the rows that pass all of them.
C2 = 2 Only rows that pass both tests count: Open and older than 4 days. The Open ticket on 3 days fails the second test.
COUNTIFS means “and”. For “or”, such as tickets that are open or waiting, add two COUNTIFs together: =COUNTIF($D$2:$D$7, "Open") + COUNTIF($D$2:$D$7, "Waiting"). It’s in every Excel since
2007 and in Google Sheets.
Excel’s counting functions differ in what they’ll count. Which one you want depends on what the column holds and what you’re asking of it.
B2 = 4 COUNT counts numbers only, so the n/a and the blank don’t count.
COUNTIF counts the rows that pass a test, so it can’t say how many different values a column holds. UNIQUE can: it lists each value once, and COUNTA counts the list.
B2 = 3 UNIQUE lists each status once, Open, Closed and Waiting, and COUNTA counts the list.
UNIQUE is in Microsoft 365, Excel 2021 and later, and Google Sheets. In older versions of Excel, the
usual formula is =SUMPRODUCT(1/COUNTIF(D2:D7, D2:D7)), which gives each value a share of
1 however often it repeats. It fails on a blank cell, so the range must be full. To see which values
repeat, not just how many there are, find duplicates with COUNTIF.
To carry on in your own Excel, download the ticket log. It’s the same job at full size: 240 support tickets with statuses, priorities and owners, some of them nobody has picked up, and a tasks sheet that counts them every way this page covers, with the answers on a sheet of their own.
240 IT support tickets with statuses, priorities, subjects, requesters and owners, some of them not yet picked up. Practice COUNTIF on text, numbers and blanks, COUNTIFS, and counting unique values.
240 rows · Ticket · Opened · Status · Priority · Subject · Requester · Owner · Days open
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 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 →