Count Support Tickets with COUNTIF and COUNTIFS
Your task: In G2:G5, count the tickets that answer each of the four questions.
Full instructions, scenario and goal
Scenario
You lead a support team. The ticket list shows each ticket's priority, status and the agent it is assigned to. Before the stand-up you want four counts, and the counts must update as agents change tickets during the day.
Your task
- Look at the tickets in A1:D15 and the four questions in F2:F5.
- In G2, count the tickets whose status is Open.
- In G3, count the tickets that are Open and also High priority.
- In G4, count the tickets that are not Closed.
- In G5, count the tickets assigned to an agent whose name starts with M.
- Write a formula in each cell, then select Check Answer.
You are done when
- All four cells contain formulas that count from the columns of the ticket list.
- The counts follow the data when statuses, priorities or agents change.
- Each count answers its own question: pay attention to the words 'and', 'not' and 'starts with'.
What you will practise
Count rows that meet one or several conditions, and write criteria for 'not equal to' and 'starts with'.
The idea in 30 seconds
COUNTIF counts how many cells in a range meet one condition; COUNTIFS counts the rows that meet several conditions at once. COUNTIFS takes pairs: a range, then the condition for that range. A row is only counted when every pair is true, so the ranges must be the same size.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell G2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
COUNTIF counts how many cells in a range meet one condition; COUNTIFS counts the rows that meet several conditions at once. COUNTIFS takes pairs: a range, then the condition for that range. A row is only counted when every pair is true, so the ranges must be the same size.
A criterion can be an exact word such as "Closed", a number, or text that starts with an operator. "<>Closed" means 'anything except Closed', and ">=10" means 'ten or more'. Wildcards work in text criteria: * stands for any run of characters and ? for exactly one, so "A*" matches everything that starts with A. Criteria are not case sensitive.
Skills you will practise
- COUNTIF
- COUNTIFS
- Criteria
- Wildcards
Common mistakes to avoid
- Using COUNTIF for two conditions
- COUNTIF accepts only one range and one criterion. A second condition needs COUNTIFS.
- Forgetting the quotation marks around an operator
- The operator is part of the criterion, so it goes inside the quotation marks together with the word it applies to.