The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color.

The Color field has been added as a Row field to group data by color. The Color field has also been added as a Value field, and renamed “Count”:

The Date field has been added as a Column field and grouped by month:

Helper column alternative

As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year. Then add the Year field to the pivot table directly.

COUNTIFS alternative

As an alternative to a pivot table, you can use the COUNTIFS function to count by month, as seen in this example.




Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.   

Pivot table count by month - 14Pivot table count by month - 59Pivot table count by month - 86