Excel COUNTIF and COUNTIFS
COUNTIF counts the cells that meet one condition: =COUNTIF(range, criteria). COUNTIFS counts the rows where several conditions are all true: =COUNTIFS(range1, criteria1, range2, criteria2, ...).
=COUNTIF(range, criteria) | COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
range- The cells to look at, for example the Status column.
criteria- The condition: text such as
"Active", a number, a cell such asF2, a comparison such as">80", or text with wildcards such as"Sales*". criteria_range, criteria (COUNTIFS)- Pairs of a range and its condition. All ranges must have the same size, and a row is counted only when all conditions are true. COUNTIFS has no separate range to count, so unlike SUMIFS it starts straight with the first pair.
How COUNTIF works
COUNTIF looks at every cell in a range and counts the ones that match your condition. It always returns a whole number, and it never adds anything up. In the sheet below, the status of each person is in C2:C13. To count the people whose status is Active:
=COUNTIF(C2:C13, "Active")
- The first argument is the range to look at:
C2:C13. - The second argument is the condition: the text
"Active". - Excel checks the 12 cells, and returns 8.
To add up numbers that meet a condition instead of counting them, use SUMIF.
Writing the criteria
- Text goes in quotes:
"Active". Matching ignores capital letters, and the whole cell must match. - A cell goes in without quotes:
F7. Changing the cell changes the count. - A comparison goes in quotes, operator first:
">80","<=50","<>Left"(not Left). - A comparison with a cell joins the operator and the cell with
&:">"&F4. Writing">F4"in quotes compares against the text "F4", and nothing matches. - Empty and not empty:
"<>"counts cells that are not empty, and"="counts cells that are empty.
Wildcards
In text criteria, * stands for any number of characters, including none, and ? for exactly one character. The team names in column B are Sales EU, Sales US, Support, Dev EU and Dev US:
"Sales*"matches Sales EU and Sales US: everything that starts with Sales."*EU"matches Sales EU and Dev EU: everything that ends with EU."*ales*"matches names that contain ales anywhere."Sales"with no wildcard matches only cells that are exactly Sales, so here it counts nothing.
Wildcards also work from a cell: if F7 contains Dev*, then COUNTIF(B2:B13, F7) counts every Dev team.
COUNTIFS: more than one condition
COUNTIFS takes pairs of a range and a condition, and counts a row only when every pair is true. To count active people on any Dev team:
=COUNTIFS(B2:B13, "Dev*", C2:C13, "Active")
COUNTIFS also works with one condition, so it can replace COUNTIF everywhere.
Counting between two numbers
There is no "between" criterion, so use two conditions on the same range: one for the lower limit and one for the upper limit.
=COUNTIFS(D2:D13, ">="&F10, D2:D13, "<="&G10)
Choose the operators deliberately. >= and <= include the limits themselves, while > and < leave them out. A score of exactly the top limit is counted only with <=.
Practise COUNTIF and COUNTIFS
Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Status | Score | Criteria | Count | |||
| 2 | Anna | Sales EU | Active | 72 | Active | ||||
| 3 | Boris | Support | Active | 85 | Sales* | ||||
| 4 | Clara | Sales US | Left | 64 | 80 | ||||
| 5 | David | Dev EU | Active | 91 | |||||
| 6 | Eva | Dev US | Active | 58 | Team | Status | Count | ||
| 7 | Filip | Support | Left | 77 | Dev* | Active | |||
| 8 | Greta | Sales EU | Active | 88 | |||||
| 9 | Hugo | Dev EU | Left | 69 | From | To | Count | ||
| 10 | Ines | Support | Active | 95 | 70 | 85 | |||
| 11 | Jan | Sales US | Active | 80 | |||||
| 12 | Kaja | Dev US | Active | 74 | |||||
| 13 | Luka | Support | Left | 66 | |||||
| 14 | |||||||||
| 15 |
-
1In
G2, count how many people have the status"Active"(column C). Type the text"Active"into the formula.Show answer
=COUNTIF(C2:C13,"Active") -
2In
G3, count the people whose team (column B) starts withSales, that is Sales EU and Sales US. Use a wildcard.Show answer
=COUNTIF(B2:B13,"Sales*") -
3In
G4, count the people whose score (column D) is higher than the number inF4.Show answer
=COUNTIF(D2:D13,">"&F4) -
4In
H7, count the people whose team matchesF7and whose status matchesG7.F7holds a wildcard, which works from a cell too.Show answer
=COUNTIFS(B2:B13,F7,C2:C13,G7) -
5In
H10, count the people whose score (column D) is between the numbers inF10andG10, including both limits.Show answer
=COUNTIFS(D2:D13,">="&F10,D2:D13,"<="&G10)
Common COUNTIF and COUNTIFS problems
The result is 0 and you expected a count
Nothing matched. Check the spelling and look for extra spaces in the data (TRIM removes them). A cell reference inside quotes, or a comparison written as ">F4", is a common cause. Also remember that text without a wildcard must match the whole cell: "Sales" does not match "Sales EU".
Numbers stored as text
A number saved as text does not match a numeric comparison such as ">80". Convert the column to real numbers.
#VALUE! in COUNTIFS
All criteria ranges in COUNTIFS must have the same number of rows and columns. Ranges of different sizes give #VALUE!.
Counting long text or codes goes wrong
COUNTIF cannot match text longer than 255 characters, and it treats a code of 16 or more digits as a number rounded to 15 digits, so different long numbers can look the same. For those, use SUMPRODUCT with an exact comparison.
Right in the first cell, wrong after filling
The range moved. Lock it with dollar signs, for example $C$2:$C$13, and leave the criteria cell relative if it should change from row to row.
Questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF has one range and one condition. COUNTIFS takes any number of range and condition pairs and counts the rows where all of them are true. COUNTIFS also works with a single condition.
How do I use a cell reference with ">" in COUNTIF?
Join the operator and the cell with an ampersand: =COUNTIF(D2:D13, ">"&F4). Writing ">F4" in quotes compares against the text "F4" and counts nothing.
How do I count cells that contain certain text?
Put asterisks around the text: =COUNTIF(B2:B13, "*Sales*") counts every cell that contains Sales anywhere. For text at the start, use "Sales*", and at the end "*EU".
How do I count the cells between two numbers?
Use COUNTIFS with two conditions on the same range: =COUNTIFS(D2:D13, ">="&F10, D2:D13, "<="&G10). Use > and < instead if the limits themselves should not be counted.
How do I count non-empty or empty cells?
=COUNTIF(range, "<>") counts cells that are not empty, and =COUNTIF(range, "=") counts cells that are empty. COUNTA and COUNTBLANK do the same jobs without criteria.
Is COUNTIF case-sensitive?
No. "active", "Active" and "ACTIVE" all match each other. To count with capital letters taken into account, use SUMPRODUCT with EXACT.