How to Count Status Categories by Year Using an Excel PivotTable
Question details
The user needs to count specific project status categories (e.g., Not Started, In Progress, Completed) and separate the totals by year across a multi-year dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing project tracking data containing dates and status categories across multiple years.
- Observed behavior
- Instead of a simple total count for each status, the user requires a breakdown showing the count of each status per year.
Ensure that your source data is organized with clear column headers and that the dates in your 'Date' column are formatted as valid Excel dates, not as text.
Use a PivotTable with Date Grouping
This is the most efficient method to summarize and count text categories while automatically breaking down the dates into years.
A PivotTable allows you to quickly aggregate text data using the Count function. By leveraging the built-in Grouping feature, you can automatically convert daily dates into a clean, year-over-year summary without writing complex COUNTIFS formulas.
Select your source data range. Go to the Insert tab on the ribbon and click PivotTable, then choose to place it on a new or existing worksheet.
In the PivotTable Fields pane, drag the 'Date' field to the Rows area and the 'Action' or 'Status' field to the Columns area.
Drag the 'Action' or 'Status' field again, this time into the Values area. Excel will automatically default to 'Count' since the data contains text.
Right-click any date in the Row Labels of the PivotTable, select 'Group', and choose 'Years' from the grouping dialog box. You can also select 'Quarters' or 'Months' simultaneously if needed.
Create and Group PivotTables Easily with WPS Spreadsheet
WPS Spreadsheet provides a powerful and user-friendly PivotTable feature that allows you to summarize status categories and group dates by year with just a few clicks.
- 1. Open Data in WPS: Launch WPS Spreadsheet and open your project tracking worksheet.
- 2. Insert PivotTable: Navigate to the Insert tab, click PivotTable, and confirm your data range.
- 3. Drag Fields: Drag 'Date' to the Rows area, 'Status' to Columns, and 'Status' to Values (ensure it is set to Count).
- 4. Group by Year: Right-click the date items in the PivotTable, click Group, and select Years to complete the setup.
Frequently Asked Questions
Why is the 'Group' option grayed out in my PivotTable?
The 'Group' option becomes unavailable if your Date column contains blank cells, text strings, or improperly formatted dates. Ensure all cells in the Date column are formatted as dates.
Can I use COUNTIFS instead of a PivotTable to get the same result?
Yes, you can use a COUNTIFS formula by specifying both the status criteria and the date range (e.g., >= Jan 1 and <= Dec 31 of a specific year), but a PivotTable is much faster and requires no complex formulas.
How do I group my PivotTable by both Year and Month?
When you right-click a date in the PivotTable and select Group, you can click on multiple options in the dialog box. Highlight both 'Years' and 'Months' to see a detailed breakdown.
What if my status category shows 'Sum' instead of 'Count'?
If your status column contains numbers, Excel might default to 'Sum'. Right-click any value in the PivotTable, choose 'Summarize Values By', and select 'Count'.
