Skip to content
Courses

COUNTIF

COUNTIF is an Excel and Google Sheets function that counts the cells in a range that meet one condition: =COUNTIF(range, criteria).

Select any words here to ask your Maestro about them.

In B2 you write =COUNTIF(A2:A9, A2) and copy it down to B9. Order 1043 is in A3 and again in A7. What does B7 show?

Orders exported from an online store. Example data.

How it works: =COUNTIF(range, criteria). Criteria can be text ("Austin"), a comparison (">100"), a cell (A2) or a wildcard ("late"), and it ignores upper and lower case. For two conditions or more, use COUNTIFS. To add instead of count, use SUMIF. Same in Google Sheets.

Finding duplicates: next to each row, a COUNTIF that counts how many times that row's value appears in the column. Anything above 1 is a duplicate, and conditional formatting can highlight them.

COUNT, COUNTA or COUNTIF: COUNT counts cells with numbers, COUNTA counts cells that aren't empty, and COUNTIF counts the ones that meet your condition. To count every category at once, a pivot table is faster.

How it's used

Four kinds of criteria

Four ways to use it

  1. =COUNTIF(C2:C9, "Austin")Text: it ignores upper and lower case.
  2. =COUNTIF(D2:D9, ">100")A comparison goes inside quotes.
  3. marked: =COUNTIF(E2:E9, "*late*")The * stands for any text: this counts every cell that contains late.
  4. =COUNTIFS(C2:C9, "Austin", D2:D9, ">100")Two conditions: a range and its criteria, in pairs.
A comparison goes in quotes because it's text with a symbol in front. A plain number or a cell reference doesn't need them.

When to look closer

Card numbers and long IDs

Looking for repeated IDs

  1. A2 holds the ID 4111222233334441 and A5 holds 4111222233334442, both as text
  2. marked: =COUNTIF(A2:A9, A2)

A2 and A5 hold two 16-digit IDs that differ only in the last digit. What does =COUNTIF(A2:A9, A2) return?

Ask your Maestro about COUNTIF

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "COUNTIF" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. What do you count by hand in your sheets, category by category?

Asking about: Answers from the page "Why does pandas count "CDMX" and "cdmx " as different cities?"

Only this browser keeps what you ask here. How it works

Learn more

Related terms

  • SUMIF

    SUMIF is an Excel and Google Sheets function that adds up only the cells that meet one condition, like one store's sales: =SUMIF(range, criteria, sum_range).

  • Pivot table

    A pivot table summarizes a long table in seconds: it groups by the columns you pick and sums, counts or averages another, with no formulas.

  • Conditional formatting

    Conditional formatting changes a cell's color or style based on its value, with rules like "greater than", "duplicates" or a color scale, and updates on its own when the data changes.

  • XLOOKUP

    XLOOKUP looks for a value in one column and returns the data from the same row in another column, to the right or the left: =XLOOKUP(E2,B2:B9,C2:C9).

The whole glossary