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.

 

[t0azt]

Use countif without GETPIVOTDATA. Assuming your data starts in A1 COUNTIF(B1:B13,">1")

 

[Doghouse308]

I should have been more specific. Yes, I am aware that I can use the countif on the column, but my goal is to keep the entire project as dynamic as possible. There are no hard-coded references in the workbook. The other data preparation tables are using GETPIVOTDATA to provide the data needed for the tables and charts. I know I can create charts directly from the pivot tables, but do not have the options that I do with standard tables, hence the data preparation tables.

 

[t0azt]

I dont think you can do that. If you want to use getpivtodata you'd have to write out a countif for each condition in your pivot table and add them. You can't use wildcards in getpivotdata as far as I know so the formula would be very long and problematic.

 

[t0azt]

You can try this formula. If your pivot table is in A1 (so the first 1 is in B2) this formula will grow as your range grows.

=COUNTIF(B2:INDEX(B:B,MATCH(1E+99,B:B)-1),">1")

 

[Doghouse308]

Thank you for your suggestion. But this approach has the same problem as the first suggestion. I have gone to great lengths to avoid hard-coded references. The problem with that approach is if the pivot table is expanded or moved, the formula will not work.

 

[Marcelo Branco]

Thank you for your suggestion. But this approach has the same problem as the first suggestion. I have gone to great lengths to avoid hard-coded references. The problem with that approach is if the pivot table is expanded or moved, the formula will not work.

Maybe this

Pivot table name = PivotTable1

	A​	B​
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​

<tbody>
</tbody>

Alt+F11 to open VBEditor
Menu
Insert > Module
Paste the code below in the right panel

Function GetCountIf(sPVT As String, sField As String, val As String)
    GetCountIf = Application.CountIf(ActiveSheet.PivotTables(sPVT).PivotFields(sField).DataRange, val)
End Function

Excel
To get = 2
=GetCountIf("PivotTable1","Count of Code", "=2")

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

Hope this helps

M.

 

[Doghouse308]

Thank you! That looks great. I'll check on that this afternoon or evening!

 

[Marcelo Branco]

You are welcome. I hope it works for you.

M.

 

[Marcelo Branco]

Thank you! That looks great. I'll check on that this afternoon or evening!

I noticed the function doesn't update when the PVT is refreshed

Maybe.. (not fully tested)

Function GetCountIf(sPVT As String, sField As String, val As String)
    Application.Volatile
    GetCountIf = Application.CountIf(ActiveSheet.PivotTables(sPVT).PivotFields(sField).DataRange, val)
End Function

M.