Can I use a condition in GETPIVOTDATA?

[Doghouse308]

I have a pivot table that is tracking occurrence per device as follows:

[TABLE="width: 225"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]PJ1065[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ1089[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ1156[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ1197[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ1573[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PJ1754[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ3159[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ3223[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]PJ3648[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ3944[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ4064[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ4073[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PJ4309[/TD]
[TD="align: right"]1

[/TD]
[/TR]
</tbody>[/TABLE]


I need to find a way to count the number of instances with 2 and with 3 or more occurrences per device. I tried using GETPIVOTDATA, but cannot find a way to count conditionally. I also tried using the GETPIVOTDATA inside of a countif without success.

I greatly appreciate any suggestions. Once I have this one last piece of the puzzle I can complete a dashboard project.

 

[Doghouse308]

I'll try using the function inside of an onchange to refresh the data preparation table.

 

[Marcelo Branco]

I'll try using the function inside of an onchange to refresh the data preparation table.

A workaround that worked for me.
I added a new argument to the function: a range, a cell, that belongs to the PVT - just to force function to recalculates when the PVT is refreshed

Something like this

PivotTable1 in, say, A3:Bn

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Code​

[/TD]
[TD]

Count of Code​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

PJ1065​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

PJ1089​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

PJ1156​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

PJ1197​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

PJ1573​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

PJ1754​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

PJ3159​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

PJ3223​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

PJ3648​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

PJ3944​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

PJ4064​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

PJ4073​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

PJ4309​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Grand Total​

[/TD]
[TD]

16​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Function (new version)

Function GetCountIfV2(sPVT As String, sField As String, val As String, [COLOR=#ff0000]r As Range[/COLOR])
    GetCountIfV2 = Application.CountIf(ActiveSheet.PivotTables(sPVT).PivotFields(sField).DataRange, val)
End Function

Excel
to get =2
=GetCountIfV2("PivotTable1","Count of Code","=2",A3)

to get >=2
=GetCountIfV2("PivotTable1","Count of Code",">=2",A3)

M.

 

[Marcelo Branco]

If you want "to avoid hard-coded references", as you said in post 6, maybe you can use this event (Not tested)

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    'code here
End Sub

M.