# GetPivotData

#### rhombus4

##### Well-known Member
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
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
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
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
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.

Replies
1
Views
110
Replies
6
Views
91
Replies
1
Views
363
Replies
9
Views
110
Replies
4
Views
90

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.

### Which adblocker are you using?

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

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