COUNTIF practice in Google Sheets

COUNTIF counts the cells in a range that meet one condition. In Google Sheets it takes the same two arguments as in Excel and understands the same operators and wildcards.

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

Questions

How do I count cells that are not empty with COUNTIF in Google Sheets?

Use "<>" as the criterion: =COUNTIF(A2:A10,"<>") counts every cell in A2:A10 that is not empty, as it does in Excel.

Can COUNTIF count cells by background color in Google Sheets?

No. COUNTIF only looks at the values in the cells, not their formatting, and Sheets has no built-in function for colors. The reliable fix is a helper column that holds the reason for the color, like Overdue, so you can count that value instead. Then the count keeps working when colors change.