Can I use a condition in GETPIVOTDATA?

Doghouse308

New Member
Joined
May 28, 2019
Messages
13
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Use countif without GETPIVOTDATA. Assuming your data starts in A1 COUNTIF(B1:B13,">1")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
=COUNTIF(B2:INDEX(B:B,MATCH(1E+99,B:B)-1),">1")
 
Upvote 0
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.
 
Upvote 0
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
Code:
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.
 
Last edited:
Upvote 0
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)

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top