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.

 

[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

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

A​

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

B​

[/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]
</tbody>[/TABLE]


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.