Pivot tables are an easy way to quickly count unique values in a data set, and can easily be adapted to perform a two-way count. In the example shown above, a pivot table is used to count unique combinations of color and size, based on data in the range B5:D16, defined as an Excel Table.
The pivot table shown is based on three fields: Color, Size, and Qty. The Color field is configured as a Rows field, and the Size field is configured as a Columns field. The Color field is also configured as a Value field.
The Color field is configured to summarize by count in the Values area:
Because the colors are text values, the pivot table automatically performs a count instead of a sum. You are free to rename "Count of Color" as you like.
- Define data as an Excel Table (optional)
- Create a pivot table based on table (or data)
- Add Color field to the Rows area
- Add Size field to the Columns area
- Add Colors field to the Values area
- Any non-blank field in the data can be used in the Values area to get a count.
- When a text field is added as a Value field, Excel will display a count automatically.