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.


How to Apply Excel COUNTIF Function with Pivot Table Calculated Field: with Easy Steps

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.

📌 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

Note:

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.


📌 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.

=COUNTIF(L4:L8,">=100")

Applying Excel COUNTIF with Pivot Table Calculated Field

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


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.


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

In this article, you have found how to use the COUNTIF function in Pivot Table Calculated Field. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

 

 

ExcelDemy
Logo