How to Use COUNTIFS in Google Sheets
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]).
What COUNTIFS does
COUNTIF counts with one condition. COUNTIFS counts with as many as you need, such as "how many tasks belong to Ann and are Done?" Only rows that pass every test are counted.
COUNTIFS syntax
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
criteria_range1: the first range to test.criterion1: the test for that range, such as"Done"or"<30".criteria_range2, criterion2, ...(optional): more pairs.
Google's help page says every additional range must have the same number of rows and columns as the first range.
A step-by-step example
Here is the task list in cells A1:D8.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Owner | Status | Hours |
| 2 | Write report | Ann | Done | 3 |
| 3 | Fix bug | Ben | Open | 5 |
| 4 | Send invoice | Ann | Done | 2 |
| 5 | Plan trip | Cara | Open | 6 |
| 6 | Update docs | Ben | Done | 4 |
| 7 | Review PR | Ann | Open | 1 |
| 8 | Test app | Ben | Done | 8 |
To count Ann's tasks that are Done:
=COUNTIFS(B2:B8, "Ann", C2:C8, "Done")
This returns 2. Here is why:
- Ann owns rows 2, 4 and 7.
- Of those, rows 2 and 4 are Done.
- Two rows pass both tests, so the result is 2.
Combine text and number tests
To count Done tasks that took more than 2 hours:
=COUNTIFS(C2:C8, "Done", D2:D8, ">2")
This returns 3 (the 3, 4 and 8 hour tasks). The operators =, <>, >, >=, < and <= work as they do in COUNTIF. To compare with a cell, join it with an ampersand: ">"&F1.
Common COUNTIFS mistakes
- Ranges of different sizes.
B2:B8withC2:C7causes an error. - Expecting OR. All conditions must be true. For "Ann or Ben", add two COUNTIFS.
- Missing quotation marks around text or operator tests.
- Hidden spaces in the data, which stop
"Done"from matching"Done ".
Try it yourself
Use the task list above (A1:D8). Type your formula into the box, starting with =, then press Check.
Exercise 1: Owner and status
Write a formula that counts the tasks where the Owner is Ann and the Status is Done.
Exercise 2: Done and long
Write a formula that counts the Done tasks that took more than 2 hours (column D).
What to learn next
- COUNTIF: count the rows that match a condition.
- SUMIFS: add up values that meet several conditions.
- FILTER: pull out the rows that match.
- Highlight a row based on a cell: color a whole row when one cell matches.
- AVERAGEIFS: averages values that meet several conditions.
Syntax and rules in this guide were checked against Google's COUNTIFS help page. See all guides.