Can I use a condition in GETPIVOTDATA?

[Doghouse308]

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

PJ1065	1
PJ1089	1
PJ1156	1
PJ1197	1
PJ1573	2
PJ1754	1
PJ3159	1
PJ3223	3
PJ3648	1
PJ3944	1
PJ4064	1
PJ4073	1
PJ4309	1

<colgroup><col><col></colgroup><tbody>
</tbody>

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

	A​	B​
1​
2​
3​	Code​	Count of Code​
4​	PJ1065​	1​
5​	PJ1089​	1​
6​	PJ1156​	1​
7​	PJ1197​	1​
8​	PJ1573​	2​
9​	PJ1754​	1​
10​	PJ3159​	1​
11​	PJ3223​	3​
12​	PJ3648​	1​
13​	PJ3944​	1​
14​	PJ4064​	1​
15​	PJ4073​	1​
16​	PJ4309​	1​
17​	Grand Total​	16​
18​

<tbody>
</tbody>

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.