How to Apply Excel COUNTIF with Pivot Table Calculated Field

Get FREE Advanced Excel Exercises with Solutions!

You have come to the right place if you are looking for the answer or some unique tips to use the COUNTIF function with pivot table calculated field in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Letโ€™s move on to the articleโ€™s main discussion.

Download Practice Workbook

You can download the practice workbook from here:

Steps to Apply COUNTIF Function in Excel with Pivot Table Calculated Field

In this section, I will show you the quick and easy steps to use the COUNTIF function with the pivot table calculated field in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

Here I have a dataset of sales information of certain products. I want to calculate how many days at which total sales cross 100 units. For this, I have to create a pivot table, apply a condition with the calculated field, and after that have to use the COUNTIF function.

sample dataset to Apply Excel COUNTIF with Pivot Table Calculated Field

๐Ÿ“Œ Step 1: Create Pivot Table from Dataset

  • First, you have to make the dataset ready.
  • Then,ย  click on any cell of the dataset and go to the Insert tab in the top ribbon.
  • Here, select the PivotTable option.

Inserting Pivot table for dataset

  • After that, a new window will appear.
  • Here, you will see that the table range is already selected. Just recheck the data range.
  • Then, select the New Worksheet option or Existing Worksheet option as per your wish where you want to insert the pivot table.
  • Finally, press OK.

Selecting data range for pivot table

  • As a result, a blank pivot table will be created at the specified location, and a window will open named PivotTable Fields on the right side of the Excel worksheet.
  • Here, mark the Date and Units option. So, the pivot table will show the total units sold per day.

Applying criteria in Pivot table fields window

  • Now, click on the pivot table and you will see a new tab will appear on the top ribbon named PivotTable Analyze. Go to that tab.
  • Under this tab, click on the Fields, Items & Sets menu and select the Calculated Field option.

Read More: COUNTIF Excel Example (22 Examples)

Similar Readings

๐Ÿ“Œ Step 2: Insert Calculated Field in Pivot Table

Inserting Pivot Table Calculated Field in Excel

  • Then a new window will appear named โ€œInsert Calculated Fieldโ€.
  • Here insert the following formula to count days on which total sales crossed 100 units.
= Units > 100


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

Giving formula in Pivot Table Calculated Field

  • After that, you will see that a new column is created at the end of the pivot table.
  • In this column, 1 denotes that sales have crossed 100 on that day and 0 denotes the negative.

Read More: VBA COUNTIF Function in Excel (6 Examples)

๐Ÿ“Œ Final Step: Use COUNTIF Function with Calculated Field Column

  • Now, insert the following formula to calculate the count of days at which sales per day crossed 100 units.


Applying Excel COUNTIF with Pivot Table Calculated Field

Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)

How to Use Pivot Table Calculated Field with Excel If Statement

In the calculated field of the pivot table, you canโ€™t use any Excel functions like IF, COUNTIF, or any others. If you want to count or sum for any conditions apply that condition in the Formula box without using any Excel functions. If the condition is filled then there will be inserted 1 else 0 will be inserted.


In this article, you have found how to use the COUNTIF function in Pivot Table Calculated Field. I hope you found this article helpful. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.

Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF