GetPivotData

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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})),"")
 

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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"),"")
 

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,823
Messages
5,743,406
Members
423,792
Latest member
travisds

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
Top