Count Things With a Pivot Table in Excel (Tickets by Status and Priority)
A pivot table can count rows, not just add up numbers. Count support tickets by status, split them by priority, and check one answer with COUNTIF, step by step in Excel.
Try it yourself
Copy this table into a blank sheet, then follow the steps below.
Paste at cell A1:
| Ticket | Summary | Status | Priority |
|---|---|---|---|
| T-101 | Login fails on mobile | Open | High |
| T-102 | Invoice total wrong | In progress | High |
| T-103 | Search page is slow | Open | Medium |
| T-104 | Typo on checkout page | Done | Low |
| T-105 | Reset email arrives late | Open | High |
| T-106 | Export button missing | In progress | Medium |
| T-107 | Wrong tax rate | Done | High |
| T-108 | Broken image on home | Done | Low |
| T-109 | Cart empties on refresh | Open | High |
| T-110 | Filter ignores dates | In progress | Low |
| T-111 | Report times out | Open | Medium |
| T-112 | Menu covers the logo | Done | Medium |
Step by step
- Count with a pivot. How many tickets are open? How many are high priority? A pivot table counts rows for you, no formulas needed. Let's build one in Excel.
- The tickets. Here's a list of twelve support tickets. Each one has an ID, a summary, a status and a priority. One header row, no gaps.
- Insert pivot table. Select the list, then Insert, Pivot Table. I'll put it on the same sheet, next to the data.
- Status in rows. Drag status into rows. Excel lists each status once: done, in progress and open.
- Count the tickets. Now drag ticket into values. A ticket ID is text, so Excel can't add it up. It counts it instead. That's the trick: put any text column in values to count rows. Four done, three in progress and five open. Twelve in total, the same as our list.
- Priority in columns. Drag priority into columns. Now every status is split by high, low and medium.
- Read the answer. Look where open meets high. Three open tickets are high priority. That's the number your manager asks for in the stand up.
- Check with COUNTIF. Always check a new pivot once. Below it, type the word open. Next to it, count it with a formula. Equals count if, the status column, then the cell with open. It gives five, the same as the pivot.
F11
OpenG11
=COUNTIF(C2:C13,F11) - The whole summary. Twelve rows became one small table. Rows and columns are how you group. Values is what you count.
- Counting rules. So, to count rows, put a text column like the ID in values, and Excel counts instead of adding. When tickets change, right click and refresh.
