How to Apply the Excel COUNTIF Function in a Pivot Table Calculated Field


The dataset showcases sales information.

Use the COUNTIF function to calculate the number of days with total sales greater than 100 units.

sample dataset to Apply Excel COUNTIF with Pivot Table Calculated Field

Step 1 – Create a Pivot Table 

  • Click any cell in the dataset and go to the Insert tab.
  • Select PivotTable.

Inserting Pivot table for dataset

  • Select New Worksheet or Existing Worksheet to insert the pivot table.
  • Click OK.

Selecting data range for pivot table

  • A blank pivot table will be created.
  • In the PivotTable Fields window, check Date and Units.

Applying criteria in Pivot table fields window

  • Click the pivot table and go to PivotTable Analyze.
  • Click Fields, Items & Sets and select Calculated Field.

Step 2 – Insert a Calculated Field in the Pivot Table

Inserting Pivot Table Calculated Field in Excel

  • In the “Insert Calculated Field” window, enter the following formula.
= Units > 100


You can insert the fields by selecting them from the lists and clicking Insert Variable.

Giving formula in Pivot Table Calculated Field

  • A new column is created in the pivot table.
  • In this column, 1 refers to sales greater than 100 units on that day.

Final Step: Use the COUNTIF Function with a Calculated Field Column

  • Enter the following formula to calculate the count of days with sales greater than 100 units per day.


Applying Excel COUNTIF with Pivot Table Calculated Field

Read More: How to Get a Count in Excel Pivot Table Calculated Field

How to Use an Excel If Statement in a Pivot Table Calculated Field

In the calculated field of the pivot table, it’s not possible to use Excel functions like IF or COUNTIF. To count or sum use the Formula box without Excel functions.

Download Practice Workbook

Download the practice workbook here.


Related Articles

<< Go Back to Pivot Table Calculations | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF