How to create a COUNTIF formula in Pivot Tables using VBA

Pivot Tables are an essential feature in Excel, designed to help users quickly summarize and analyze large data sets. By simply dragging and dropping fields, you can create detailed reports that display sums, averages, counts, and other aggregations of your data.

COUNTIF is a widely used Excel function that counts the number of cells within a range that meet a single condition. It's particularly useful when you need to count specific values or criteria within a dataset. However, applying a COUNTIF formula directly within a Pivot Table isn’t possible through the standard Excel interface.

This limitation can be overcome by using VBA to create Pivot Tables that incorporate custom functionalities, like the ability to count cells based on specific criteria. By leveraging VBA, you can automate the process of applying a COUNTIF-like functionality in your Pivot Tables.

Before diving into the VBA solution, it’s important to understand why COUNTIF cannot be directly applied within a Pivot Table. Pivot Tables inherently work with aggregated data, and they do not allow for direct cell-by-cell evaluations like COUNTIF does in regular Excel ranges.

In a standard Pivot Table, you can count the number of occurrences of a specific value by placing a field in the "Values" area and setting it to "Count." However, this counts all instances without applying any conditions. 

By using VBA to create Pivot Tables, you can bypass these limitations and implement a solution that applies conditions directly within the Pivot Table structure.

Before writing the VBA code, let’s set up an example Excel workbook that we can use to demonstrate this process.

1. Create a Data Set: First, create a simple data set. For example, a table with columns like “Product,” “Quantity,” and “Sales.” Populate it with sample data.

Now, let’s write a VBA script that will create the pivot table and then apply a COUNTIF-like function to this Pivot Table. The goal is to count how many times each product has generate sales > 100.

1. Open the VBA Editor: Press Alt + F11 to open the VBA editor in Excel.

2. Insert a Module: In the VBA editor, insert a new module by clicking Insert > Module.

3. Write the VBA Code: Copy and paste the following VBA code into the module.

Sub CreatePivotTableWithCalculatedField()

    Dim wsData As Worksheet

    Dim wsPivot As Worksheet

    Dim ptCache As PivotCache

    Dim pt As PivotTable

    ' Set worksheets

    Set wsData = ThisWorkbook.Sheets("Data")

    Set wsPivot = ThisWorkbook.Sheets.Add

    wsPivot.Name = "PivotTableSheet"

    ' Create Pivot Cache

    Set ptCache = ThisWorkbook.PivotCaches.Create( _

        SourceType:=xlDatabase, _

        SourceData:=wsData.Range("A1").CurrentRegion)

    ' Create Pivot Table

    Set pt = ptCache.CreatePivotTable( _

        TableDestination:=wsPivot.Range("A3"), _

        TableName:="SalesPivotTable")

    ' Set up the Pivot Table

    With pt

        ' Add fields to the Pivot Table

        .PivotFields("Product").Orientation = xlRowField

        .PivotFields("Sales").Orientation = xlDataField

        .PivotFields("Quantity").Orientation = xlDataField

    End With

    ' Add calculated field

    pt.CalculatedFields.Add Name:="HighSalesCount", _

        Formula:="=IF(Sales>100,1,0)", UseStandardFormula:=True

    ' Add the calculated field to the Pivot Table

    pt.AddDataField pt.PivotFields("HighSalesCount"), "Count of High Sales", xlSum

    ' Refresh the Pivot Table

    pt.RefreshTable

End Sub

Explanation of the Code calculating the COUNTIF  function

Here, we add a calculated field named "HighSalesCount" that uses an IF formula to count sales greater than 100. The UseStandardFormula:=True parameter allows the use of a standard formula. 

' Add calculated field

pt.CalculatedFields.Add Name:="HighSalesCount", _

    Formula:="=IF(Sales>100,1,0)", UseStandardFormula:=True

' Add the calculated field to the Pivot Table

pt.AddDataField pt.PivotFields("HighSalesCount"), "Count of High Sales", xlSum

The Pivot Table will display a column 'Count Of High Sales' showing how many sales each product has sold for an amount > 100