COUNTIF practice exercises

Count the cells that meet a condition: a text match, a number comparison or a criterion kept in its own cell.

Start the first exercise

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.

  1. 1COUNTIF with a text conditionIn D2, use COUNTIF to count how many orders in B2:B8 came from Copenhagen.
  2. 2COUNTIF with a number conditionPremiumIn D2, use COUNTIF to count the invoices in B2:B8 of 1500 or more.
  3. 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.
  4. 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".
  5. 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.