Osman Goni Ridwan
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.
📌 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.
Read More: COUNTIF Excel Example (22 Examples)
Similar Readings
- COUNTIF vs COUNTIFS in Excel (4 Examples)
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- Excel COUNTIF with Greater Than and Less Than Criteria
- How to Apply COUNTIF Between Two Cell Values in Excel
- Use Excel COUNTIF Function to Count Cells Greater Than 0
📌 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 > 100Note:
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.
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.
=COUNTIF(L4:L8,">=100")
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.
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. 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
- Excel COUNTIF Wildcard Not Working (3 Reasons with Solutions)
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Compare Two Columns Using COUNTIF Function (4 Ways)
- Use COUNTIF Function in Excel Greater Than Percentage
- How to Use COUNTIF to Count Date Less Than Today in Excel
