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:

            COUNTIF(B2:B9, “>1000000”)

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

.