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. A pivot table can count rows, not just add up numbers. Here it answers two questions with no formulas: how many tickets are open, and how many are high priority.
- The tickets. The list in A1:D13 has 12 support tickets. Each row has a ticket ID, a summary, a status and a priority. There is one header row and no blank rows or columns.
- Insert pivot table. Select A1:D13, then go to Insert > PivotTable. Choose Existing Worksheet and pick F3, so the pivot sits next to the data. An empty pivot appears, with the PivotTable Fields list on the right.
- Status in rows. In the PivotTable Fields list, drag Status into the Rows area. Excel lists each status once: Done, In progress and Open.
- Count the tickets. Then drag Ticket into the Values area. A ticket ID is text, so Excel can't add it up. It counts it instead and shows Count of Ticket. Any text column in Values counts rows. The pivot in F3:G7 now shows 4 Done, 3 In progress and 5 Open. The Grand Total is 12, the same as the number of tickets in the list.
- Priority in columns. Drag Priority into the Columns area. Each status is now split into High, Low and Medium, with a Grand Total column on the right.
- Read the answer. Follow the Open row across to the High column, cell G7. It shows 3, so 3 open tickets are high priority. That's the number a manager asks for in a stand-up.
- Check with COUNTIF. Check a new pivot once against a formula. Below the pivot, type Open in F11. In G11, type =COUNTIF(C2:C13,F11). It counts the cells in the Status column that say Open and returns 5, the same as the pivot.
F11
OpenG11
=COUNTIF(C2:C13,F11) - The whole summary. Select F3:J8 to see the whole summary: 12 rows of tickets became one small table. Rows and Columns decide how the tickets are grouped. Values decides what is counted.
- Counting rules. To count rows, put a text column such as the ticket ID in Values, and Excel counts instead of adding. The pivot doesn't update on its own. When the tickets change, right-click the pivot and choose Refresh.
Common mistakes
- Putting a number column in Values. Excel adds up number fields by default, so you get a total, not a count. Use a text column like the ticket ID, or open Value Field Settings and choose Count.
- Counting a column that has blank cells. Count skips empty cells, so the totals come out too low. Count a column that is filled in on every row, such as the ID.
- Forgetting to refresh after the data changes. A pivot keeps showing old numbers until you right-click it and choose Refresh. New rows added below A13 are also left out unless you change the data source, so turn the list into a table with Ctrl+T before building the pivot.
- Messy status values. A typo or an extra space, like "Opn" or "Open ", shows up as its own row in the pivot. Clean the Status column, or use a drop-down list, so each status is typed the same way.
When a business analyst uses this
In a RAID log or defect log, put Status in Rows, Severity in Columns and the ID in Values. You get counts like 3 open high-severity issues for the weekly status report. Refresh the pivot before each steering meeting so the numbers match the log.
Questions people ask
How do I count in a pivot table in Excel?
Drag a text field, such as an ID, into the Values area, and Excel shows Count of that field. For a number field, open Value Field Settings and change Sum to Count.
How do I count distinct values in a pivot table?
When you create the pivot, tick Add this data to the Data Model. Then open Value Field Settings for the field and choose Distinct Count. This option is in Excel for Windows, 2013 or later, and may be missing on Mac.
How do I check that a pivot table count is right?
Check one number with a formula. For example, =COUNTIF(C2:C13,F11) counts the Open tickets in the Status column and should match the pivot's Open total.
