How to Create Pivot Table with Count of Occurrences in Google Sheets

Creating a pivot table with a count of occurrences in Google Sheets is an efficient way to analyze your data and identify trends. Here's a step-by-step guide on how to create a pivot table with a count of occurrences:

1. Open your Google Sheets document containing the data you want to analyze.
2. Click on the cell where you want to insert the pivot table, usually in a new sheet or an empty area in the existing sheet.
3. Go to the menu bar and click on "Data," then select "Pivot table." A new sheet will be created with the pivot table editor on the right side of the screen.
4. In the "Pivot table editor," you will see a section called "Rows." Click on "Add" and select the column you want to use as the row labels in the pivot table. For example, if you want to count the occurrences of different products, you would select the "Product" column.
5. Next, go to the "Values" section in the "Pivot table editor." Click on "Add" and select the same column you used for the rows. Using the previous example, you would select the "Product" column again.
6. By default, Google Sheets will sum the values in the selected column. To change this to a count of occurrences, click on the drop-down arrow next to the column name in the "Values" section, and select "COUNTA" from the list of summary functions. This function will count the number of non-empty cells in the selected column.
7. The pivot table will now display the count of occurrences for each unique value in the selected column. You can further customize the pivot table by adding more rows, columns, or filters using the "Pivot table editor."

Example

Let's create a pivot table with a count of occurrences for a list of products and their sales representatives.

1. Your data should look like this:

  A           B
1 Product     Sales Rep
2 Apples      John
3 Bananas     Jane
4 Apples      John
5 Oranges     Mike
6 Bananas     Jane
7 Apples      Mike
8 Oranges     John

2. Click on an empty cell or create a new sheet for the pivot table.
3. Go to "Data" > "Pivot table."
4. In the "Rows" section, click "Add" and select the "Product" column.
5. In the "Values" section, click "Add" and select the "Product" column again.
6. Click on the drop-down arrow next to the "Product" column in the "Values" section, and select "COUNTA."
7. The pivot table will now display the count of occurrences for each unique product:

  A           B
1 Product     COUNTA of Product
2 Apples      3
3 Bananas     2
4 Oranges     2

You can now analyze the count of occurrences in your data using the pivot table in Google Sheets.