COUNTIFS: count with two conditions
How many rows meet this condition and that one.
Task:
The service desk wants to know how many urgent tickets are still open in each team. Put the count of tickets that are both High priority and Open for the Network team in G2 and for the Desktop team in G3.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of COUNT, COUNTA
0/4The same formula in the other shapes it takes at work.
- COUNT Functions
Three ways to count, and the difference between them is the whole point.
CoreIntermediate - COUNTBLANK: what is still missing
Count the gaps rather than the entries.
Beginner - COUNTIFS: count with two conditions
How many rows meet this condition and that one.
Intermediate - COUNTIF with a wildcard
Count the cells that end with, start with or contain some text.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Ticket | Team | Priority | Status | Team | High & open | |
| 2 | T-801 | Network | High | Open | Network | ||
| 3 | T-802 | Desktop | Low | Open | Desktop | ||
| 4 | T-803 | Network | High | Closed | |||
| 5 | T-804 | Desktop | High | Open | |||
| 6 | T-805 | Network | High | Open | |||
| 7 | T-806 | Desktop | High | Open | |||
| 8 | T-807 | Network | Low | Open | |||
| 9 | T-808 | Desktop | High | Closed |
What this exercise teachesMay contain the answer
COUNTIF can only test one thing, so counting "High and Open" with it means subtracting one count from another and hoping the logic holds. COUNTIFS counts a row only when every pair is true, which is the AND you meant.