Table of Contents
To create a pivot table with unique counts in Google Sheets, first select the data range to be used in the pivot table. Then, go to the Data tab and click on “Pivot Table”. Select the columns to be included in the pivot table and click on “Summarize by” and select “Unique Count”. Finally, click on “Create” to see the pivot table with the unique counts.
The following step-by-step example shows how to create a pivot table in Google Sheets that displays the count of unique values of some variable.
Step 1: Enter the Data
First, let’s enter the following data that shows the total revenue generated by certain products in certain regions for some company:
Step 2: Create the Pivot Table
To create a pivot table that summarizes this dataset, click the Insert tab and then click Pivot table:
In the window that appears, type in the range of the data to use for the pivot table and select a cell in the existing sheet to place the pivot table:
Once you click Create, an empty pivot table will automatically be inserted.
In the Pivot table editor that appears on the right side of the screen, click Add next to Rows and choose Region.
Then click Add next to Values and click Product.
By default, Google Sheets will choose COUNTA as the way to summarize the Product values:
To instead display the unique count of products by region, click the dropdown arrow under Summarize by and click COUNTUNIQUE:
The pivot table will update to show the unique count of products by region:
For example, we can see:
- There were 3 unique products in the East region.
- There were 3 unique products in the North region.
- There were 3 unique products in the South region.
- There were 3 unique products in the West region.
And so on.
The following tutorials explain how to perform other common operations in Google Sheets: