Count open tasks
Put the following data on a sheet named Tasks. The header is row 1, and the five task records occupy rows 2 through 6.
| Row | A: Task | B: Status | C: Hours |
|---|---|---|---|
| 2 | Invoice review | Open | 3 |
| 3 | Stock check | Done | 2 |
| 4 | Client reply | Open | 5 |
| 5 | Archive | Blocked | 1 |
| 6 | Quote | Open | 4 |
=COUNTIF(Tasks!B2:B6,"Open")The result is 3, because B2, B4, and B6 contain Open. The first argument is the range to inspect. The second is the condition, with literal text inside double quotes. COUNTIF is not case-sensitive, so OPEN would also match.
Use a cell for a changeable criterion
On a summary sheet, enter Open in E2, then use:
=COUNTIF(Tasks!$B$2:$B$6,E2)This still returns 3. Change E2 to Done and it returns 1. Do not quote E2: "E2" would search for the literal characters E2. The fixed task range also lets you copy the formula next to several summary labels.
Count numeric values above a threshold
=COUNTIF(Tasks!C2:C6,">3")The result is 2, for the 5-hour and 4-hour tasks. To store the threshold in E3, enter the number 3 there and join the comparison operator to its value:
=COUNTIF(Tasks!C2:C6,">"&E3)A criterion such as ">E3" does not read the cell reference as intended. Check that the hours are actual numbers, especially after importing a CSV.
Match part of a task name
=COUNTIF(Tasks!A2:A6,"*review*")This returns 1 for Invoice review. An asterisk matches any sequence of characters; a question mark matches one character. To search for a literal asterisk, escape it with a tilde, such as "~*". See Microsoft’s COUNTIF reference for criteria details.
When one condition is not enough
For tasks that are Open and over 3 hours, both conditions must hold on the same row. Use COUNTIFS:
=COUNTIFS(Tasks!B2:B6,"Open",Tasks!C2:C6,">3")The result is 2. If you want to add those hours rather than count tasks, use SUMIFS. Neither counting formula is generated by this site yet.