How to Apply Excel COUNTIF with Pivot Table Calculated Field

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")

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:

COUNTIF with PivotTable Calculated Field.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!