Count Excel Table Priorities by Final Status with Formulas or a PivotTable
Question details
Count Excel table records for each Priority and Final Status combination, then organize the counts for a chart.
- Product
- Microsoft Excel table and PivotTable
- Device & OS
- not provided
- Scenario
- The user needs a summary matrix that lists priorities in rows, final statuses in columns, and record counts in each intersection.
- Observed behavior
- The goal is to calculate counts by priority alone or by both Priority and Final Status.
Make sure the source range is formatted as a table and that the Priority and Final Status column headers are spelled consistently. If you use a PivotTable, choose a field that is populated for every record, such as an ID or record name, for the count.
Use SUMPRODUCT formulas to count each Priority and Final Status combination
Use this method when you need a formula-based summary table that updates from the Excel table and can be used directly as a chart source.
Assuming the table is named Table1, put priority labels down column A and final-status labels across row 16. The formula uses structured references to count matching table records.
In the summary area, enter each Priority value in cells such as A17, A18, and A19.
Enter each Final Status value across the header row, starting in a cell such as C16.
To count only the priority shown in A17, select an output cell and enter =SUMPRODUCT(--(Table1[Priority]=A17)).
In cell C17, enter =SUMPRODUCT((Table1[Priority]=$A17)*(Table1[Final Status]=C$16)), then drag the fill handle across the status columns and down the priority rows.
Select the completed summary table, including its row and column labels, then use Insert > Chart and choose a column, bar, or stacked chart.
Create a PivotTable to count Priority values by Final Status
Use a PivotTable when you want to build and rearrange the count summary without writing formulas.
Count Priority and Final Status Records in WPS Spreadsheets
WPS Spreadsheets lets you build a formula-based count matrix with SUMPRODUCT or summarize the same table with a PivotTable. You can then create a chart from the summary while working with Excel workbook files.
- 1. Open the workbook in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet containing the source table.
- 2. Build a formula summary table: Enter Priority labels in one column and Final Status labels in the top row, then use =SUMPRODUCT((Table1[Priority]=$A17)*(Table1[Final Status]=C$16)) in the first result cell.
- 3. Fill the count formula: Drag the formula across and down to calculate every Priority and Final Status combination.
- 4. Create a PivotTable instead: Select a source-table cell, choose Insert > PivotTable, and place Priority in Rows, Final Status in Columns, and a populated record field in Values as Count.
- 5. Visualize the results: Select the summary range or PivotTable, choose Insert > Chart, and select a chart type suited to comparing statuses.
Frequently Asked Questions
How do I count one Priority value in an Excel table?
If the table is named Table1 and the criterion is in A17, use =SUMPRODUCT(--(Table1[Priority]=A17)). This counts rows whose Priority matches A17.
Why do the SUMPRODUCT references use $A17 and C$16?
The dollar signs keep the Priority column fixed when you fill across and keep the Final Status row fixed when you fill down. This lets one formula populate the whole count matrix.
Which field should I place in the PivotTable Values area?
Use a field that is filled in every source row, preferably a unique Record ID. Open Value Field Settings and choose Count if the PivotTable does not automatically show a count.
How can I update the PivotTable after adding new records?
Ensure the new records are inside the source table, then right-click anywhere in the PivotTable and select Refresh. The counts and associated PivotChart will update.
Can I create a chart from the formula count table?
Yes. Select the completed count matrix with its Priority and Final Status labels, then select Insert > Chart. A clustered or stacked column chart is often useful for comparing statuses by priority.
