Statistical Functions
Intermediate

COUNTIF with the threshold in a cell

Join the operator to a cell with & so the limit can change.

Task:

The service target in E1 changes each quarter. In E3 count the tickets resolved in more hours than the target, reading the target from E1.

Interactive Spreadsheet

Read about COUNTIF

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of COUNTIF

0/3

The same formula in the other shapes it takes at work.

  1. COUNTIF Function

    Counting only the rows that broke a rule.

    Core
    Intermediate
  2. Count values between two limits

    COUNTIFS on the same column twice: at least this, at most that.

    Intermediate
  3. COUNTIF with the threshold in a cell

    Join the operator to a cell with & so the limit can change.

    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.

7 rows × 5 columns1 cell you fill in
ABCDE
1TicketHours to resolveTarget (h)24
2T-112
3T-230Over target
4T-324
5T-448
6T-58
7T-626
What this exercise teachesMay contain the answer

Writing ">E1" counts values greater than the text E1 — nothing useful. The operator has to be text and the cell has to be joined on, so the criterion is rebuilt from the cell every time it recalculates. Change E1 to 36 and the count updates.