COUNTIF practice exercises
Count the cells that meet a condition: a text match, a number comparison or a criterion kept in its own cell.
How COUNTIF works
COUNTIF counts the cells in a range that meet one condition. It takes two arguments: the range to look in and the criterion. It works the same way in Excel and Google Sheets, and both understand the same operators and wildcards.
=COUNTIF(B2:B8,"Copenhagen") counts the orders that came from Copenhagen. For numbers, put the operator inside the quotes: =COUNTIF(B2:B8,">=1500") counts the invoices of 1500 or more. When the limit lives in a cell, join the operator and the cell with &, as in =COUNTIF(B2:B7,"<"&E1), so the count follows when E1 changes.
The most common slip is writing the operator and the cell reference together inside the quotes. "<E1" looks for the literal text, so you need "<"&E1. COUNTIF doesn't care about upper and lower case, so "copenhagen" matches Copenhagen. It handles one condition only. For two or more, use COUNTIFS. The exercises below cover a text condition, a number condition, a limit in a cell, everything except one value and a wildcard.
- 1COUNTIF with a text conditionIn D2, use COUNTIF to count how many orders in B2:B8 came from Copenhagen.
- 2COUNTIF with a number conditionPremiumIn D2, use COUNTIF to count the invoices in B2:B8 of 1500 or more.
- 3COUNTIF with the limit in a cellPremiumIn E2, count the products in B2:B7 with stock below the reorder level in E1. Join "<" and E1 with &, so the count follows when E1 changes.
- 4Count everything except one valuePremiumIn E1, count the tickets in B2:B8 whose status is anything but Closed. "Not equal to" is <>, so the criterion is "<>Closed".
- 5Count with a wildcardPremiumIn D1, count the products in A2:A8 with the word Chair anywhere in the name. * stands for any text, so the criterion is "*Chair*".
Functions in this topic
Read the guide
Questions
How do I use COUNTIF with multiple criteria?
Use COUNTIFS when all the conditions must be true: =COUNTIFS(B2:B8,"Copenhagen",C2:C8,">=1500") counts the rows that match both. For "this or that", add two COUNTIFs: =COUNTIF(B2:B8,"Copenhagen")+ COUNTIF(B2:B8,"Aarhus"). Add the results only if no row can match both.
Why does COUNTIF return 0 when the value is clearly there?
Usually the cell holds something slightly different: an extra space at the end, a number stored as text, or a typo in the criterion. Try the TRIM function on the data, or use a wildcard like "*Copenhagen*" to catch the extra characters. A criterion longer than 255 characters returns #VALUE!.
How do I count dates in a range with COUNTIF?
Compare against a date and join it with &: =COUNTIF(B2:B20,">="&DATE(2025,1,1)) counts every date from January 1, 2025 on. Use COUNTIFS with a start and an end date for a period. You can also put the date in a cell and write ">="&E1.