# How to count cells equal to a specific value in Excel

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

**Excel:** `=COUNTIF(B2:B12,E1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Employee | Department |  | Department | Sales |
| Lena Park | Sales |  | Employees |  |
| Omar Ruiz | IT |  |  |  |
| Noor Haddad | Sales |  |  |  |
| Tom Becker | Finance |  |  |  |
| Priya Shah | Marketing |  |  |  |
| Ines Lopez | Sales |  |  |  |
| Jon Wells | IT |  |  |  |
| Mia Chen | HR |  |  |  |
| Raj Iyer | Sales |  |  |  |
| Sara Nilsen | Finance |  |  |  |
| Ali Kaya | IT |  |  |  |

Formula in E2: `=COUNTIF(B2:B12,E1)`

Result: 4

## 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.

## Other ways

- `=SUMPRODUCT(--(B2:B12=E1))`: The 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))`: Counts 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.

## 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)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-cells-equal-to-a-value