How to Get a Count via Pivot Table in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on March 12, 2023

This tutorial demonstrates how to count records from a dataset using a pivot table in Excel and Google Sheets.


countpivot intro


Change the Pivot Table Value Field

Consider the pivot table below, based on a source dataset that has columns for each of the fields shown.

The Rows area has a field called Supplier in it, and the Values area has the sum of the Ordered field in it.


countpivot pivot table


If you wish to count, rather than sum, the Ordered field, you need to change how the Values area is calculating.

  1. Click the arrow to the right of Sum of Ordered, and then click Value Field Settings…


countpivot pivot table value field settings


  1. Select Count from the Summarize value field by list. Optionally, you can set a new Custom Name; otherwise, the name defaults to the type of calculation (e.g., Count) and the original field name (e.g., Ordered).


countpivot count


  1. Click OK to update the pivot table.


countpivot counted


  1. If you switch the field in the Rows area from Supplier to Salesperson, the value field stays a count rather than a sum, and you can then see how many orders each salesperson made.


countpivot salesperson


Tip: Try using some shortcuts when you’re working with pivot tables.

Pivot Table Count in Google Sheets

Consider the pivot table shown below.


pivotcount gs pivot table


The Ordered field has been summarized using the SUM function in the Values section of the Pivot table editor.


pivotcount gs ordered sum


Just change SUM to COUNT in the Summarize by drop down.


pivotcount gs count


The pivot table automatically updates to reflect how many orders each salesperson made rather than the added-up amount of the total orders.


pivotcount gs ordered count


See also: Count Unique Values With Pivot Table

AI Formula Generator

Try for Free

See all How-To Articles