Spreadsheet Practice

How to count cells equal to a specific value in Excel

Formula (Excel and Google Sheets)

=COUNTIF(B2:B12,E1)

Use =COUNTIF(B2:B12,E1), with the value you're counting in E1.

Result on the example
4 in E2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

How it works

COUNTIF looks through B2:B12 and counts the cells equal to E1. With the value in a cell rather than typed in the formula, you can change E1 to "IT" or "Finance" and the count updates.

The comparison ignores case, so "sales" and "Sales" are the same.

=COUNTIF(B2:B12,E1)

Result4

  1. B2:B1211 rows × 1 col: "Sales", "IT", "Sales", "Finance", …

    A range: 11 cells in one column.

  2. E1"Sales"

    The value in E1.

  3. COUNTIF(B2:B12,E1)4

    COUNTIF: Counts cells that meet one condition.

    • rangeB2:B1211 rows × 1 col: "Sales", "IT", "Sales", "Finance", …
    • criteriaE1"Sales"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:B12,E1). We ran it in Google Sheets mode on the same data and it returns 4.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--(B2:B12=E1))4The same count as an array comparison, for when you need to combine it with tests COUNTIF can't do.
=ROWS(FILTER(B2:B12,B2:B12=E1))4Counts the rows FILTER returns (Excel 2021 or later). Returns an error, not 0, when nothing matches.

Common mistakes

  • Text criteria with * or ? act as wildcards. To count a literal asterisk, use "~*".
  • A hidden trailing space ("Sales ") makes a cell not equal to "Sales". If the count looks low, see the page on COUNTIF returning 0.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count cells equal to a specific value

In E2, count the employees in the department named in E1.

Questions

How do I count a number instead of text?

The same way: =COUNTIF(C2:C12,5) counts cells equal to 5. Text that looks like 5 also counts.

How do I count the value in each department at once?

See the page on counting how many times each value appears: =HSTACK(UNIQUE(B2:B12),COUNTIF(B2:B12,UNIQUE(B2:B12))).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.