COUNTIF in a pivot table
In Microsoft Excel, the COUNTIF function is a valuable tool that enables you to determine the count of cells within a given range that satisfies specific criteria. It is widely employed for data analysis purposes and provides a means to generate counts based on defined conditions.
In this tutorial, we will learn how to use COUNTIF in a pivot table. In Excel, we can utilize the COUNTIF function to count the cells in a pivot table that satisfies specific requirements.
Here we have a pivot table representing some projects with their total worth. We want to count the number of projects with a total worth exceeding 1 Million (USD).
Method 1: Utilizing the COUNTIF Function
To count the cells of a specific requirement, we can utilize the COUNTIF function. Below are the steps to apply this function.
Step 1 – Utilize the COUNTIF Function
- Choose a blank cell, and utilize the COUNTIF function.
- The structure of the COUNTIF function would be:
Where B2:B9 is the range of cells in the pivot table.
“>1000000” is the condition to count the cells.
- Hit the Enter key.
Method 2: Counting the Cells by Adding a Calculated Feild
In Microsoft Excel, you can add a calculated field to count cells that meet a specific condition. However, Excel does not allow the use of functions in calculated fields. You can only apply conditions, resulting in a binary output of 0 or 1, representing “False” or “True” respectively.
Step 1 – Locate the PivotTable Analyze Tab
- Perform a click on any of the cells in the pivot table.
- Locate the “PivotTable Analyze” tab in the menu bar.
Step 2 – Perform a Click on the “Feild, Items & Sets” Button
- Perform a click on the “Feild, Items & Sets” button.
- Choose the “Calculated Feild” option from the drop-down menu.
Step 3 – Insert the Condition
- In the “Formula” field insert the condition.
- For the condition, select a field from the list and complete the condition.
- In this case, we will select the field “Total Worth (USD)” and complete the condition:
‘Total Worth (in USD)’ >1000000
Step 4 – Perform a Click on the “Add” Button
- Perform a click on the “Add” button and then click on the “OK” button.
Step 5 – Utilize COUNTIF on the Calculated Feild Column
- Now, we can utilize the COUNTIF function