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