CODEWITHSUNDEEP
excelexcel-formulapivot-table
Matt Reichenbach
Matt Reichenbach

Reputation: 121

Count Function for Top Level of Pivot Table

I have a pivot table that looks like this:

enter image description here

I would like to write a function that counts the number of entries in the highest level of the pivot table (ie. the A, B, C, and D). What Excel function can I write that would yield a result of 4 for this pivot table?

Thanks!

Upvotes: 1

Views: 1056

Answers (2)

PeterT
PeterT

Reputation: 8557

This key in working with pivot table fields (PivotFields) is that there are several conditions to check to make sure you're dealing with the one(s) you want. In your case, you want to make sure you're looking at fields that are Rows and are in the first position (top level).

I have a whole set of public pivot table functions that exposes these types of checks to make it easy on me. I'm using two of them to show you a solution to your question.

Option Explicit

Public Function TopLevelRows() As Long
    Dim thisWS As Worksheet
    Dim thisPT As PivotTable
    Set thisWS = ThisWorkbook.Sheets("Sheet1")
    Set thisPT = thisWS.PivotTables(1)

    Dim numberOfTopLevelRows As Long
    numberOfTopLevelRows = 0

    Dim pField As PivotField
    For Each pField In thisPT.PivotFields
        If PivotFieldIsRow(thisPT, pField.Name) And _
          (PivotFieldPosition(thisPT, pField.Name) = 1) Then
            '--- there will only be one of these, so iterate on the items
            Dim pItem As PivotItem
            '--- we can't assume that the .Count equals the number of
            '    items because there may be a "(blank)" entry
            'Debug.Print "count = " & pField.PivotItems.count
            For Each pItem In pField.PivotItems
                If pItem.Caption <> "(blank)" Then
                    numberOfTopLevelRows = numberOfTopLevelRows + 1
                End If
            Next pItem
        End If
    Next pField

    Debug.Print "there are " & numberOfTopLevelRows & " top level rows in the pivot table"
    TopLevelRows = numberOfTopLevelRows 'return value
End Function

Private Function PivotFieldIsRow(ByRef pTable As PivotTable, _
                                 ByVal ptFieldName As String) As Boolean
    Dim field As PivotField
    PivotFieldIsRow = False
    For Each field In pTable.RowFields
        If field.Name = ptFieldName Then
            PivotFieldIsRow = True
            Exit Function
        End If
    Next field
End Function

Private Function PivotFieldPosition(ByRef pTable As PivotTable, _
                                    ByVal ptFieldName As String) As Long
    Dim field As PivotField
    PivotFieldPosition = 0
    For Each field In pTable.PivotFields
        If field.Name = ptFieldName Then
            If TypeName(field.position) = "Error" Then
                '--- we'll get an error if the field is not included as a
                '    row or column. this isn't a problem, but there's no
                '    real position in this case, so return 0
            Else
                PivotFieldPosition = field.position
            End If
            Exit Function
        End If
    Next field
End Function

Now in cell A30 write the formula =TopLevelRows() and you should get your result.

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4824

Just drag the second field out of the PivotTable entirely, drag the first field from the rows area the Values area, and you will automatically get a COUNT of the number of fields if it is a text field. (Otherwise, change the aggregation from SUM to COUNT)

enter image description here

Upvotes: 0

Related Questions