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.
📌 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.
- 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.
- 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.
- 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
- 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.
- 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")
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
- Pivot Table Calculated Field for Average in Excel
- Calculated Field Sum Divided by Count in Pivot Table
- How to Calculate Weighted Average in Excel Pivot Table
- How to Calculate Variance Using Pivot Table in Excel
<< Go Back to Calculated Field in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!