GetPivotData

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
586
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Trying to Add up a Range using GETPIVOTDATA as there are too many combination of Days i.e. -5, -8, 1, 3, 8, 12, 15, 21 etc etc

Currently have
Excel Formula:
=IFERROR(GETPIVOTDATA("Category",$S$145,"Category","Equipment","Days",1),"")
which will show me the count of 1 Day

What I need is to be able to add a range or maybe non contiguous values i.e.
Want to get the Values added for 1 2 and 3 or Sum(1:3)
what formula below does is overspill and enters Count of 1 in Col W, Count of 2 in Col X and Count of 3 in Col Y
Excel Formula:
=IFERROR(GETPIVOTDATA("Category",$S$145,"Category","Equipment","Days",{1,2,3),"")

or
Want to get the Values added for 2 4 and 6
Excel Formula:
=IFERROR(GETPIVOTDATA("Category",$S$145,"Category","Equipment","Days",{2,4,6),"")
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Just wrap it in a sum formula.
PS: You seem to have lost the closing "}" in what appears in the formula copied in.

Excel Formula:
=IFERROR(SUM(GETPIVOTDATA("Category",$S$145,"Category","Equipment","Days",{1,2,3})),"")
 
Upvote 0
Thanks

Not sure where the } disappeared to lol

PS is it possible to use greater or Equal or less or Equal than I tried below but got a blank, but there are definitely some

Excel Formula:
=IFERROR(GETPIVOTDATA("Category",$S$145,"Category","Equipment","Days",">20"),"")
 
Upvote 0
also just noticed the formula only works if there is a 1 2 and 3
The example posted had a result for 1 2 and 3 but some of the others don't i.e. might have a 1 and 3 days but not a 2 and that results in a blank due to the iferror part of formula

Is it possible to add them if they don't have every day in the array
 
Upvote 0
I think the error is going to be problematic. If you are changing filters and data in such a way that an item in the criteria array will not exist then it won't just add up what does exist it will error out.

You may want to consider using sumifs on the underlying data rather than doing it this way.
I am not sure if Pivot Table Groups will achieve what you want otherwise that might be an options.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,703
Members
448,293
Latest member
jin kazuya

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