# Help with formula adding pivot data

#### dem86

##### New Member
Hi all,

I am looking for a little help with my formula. I received input from another board on how to write it but it doesn't seem to be working correctly.

I have a pivot table where only 1 condition out of the 3 that I am trying to add together has data. It is returning a 0 and not 134 like I would expect.

Any help would be appreciated.

=LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","002","Month",2)+GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","003","Month",2)+GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","005","Month",2)))

I am trying to prevent the ref error if data does not exist in all or any of the 3 areas.

Thanks again.

Doug

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### DonkeyOte

##### MrExcel MVP
If any of the GETPIVOTDATA calls returns a #REF! error then the result will be 0 (given 1+#REF! = #REF!)

You need to either:

a) add a handler to each GETPIVOTDATA call (so each returns 0 rather than #REF!)

or

b) if you expect only one of the calls to ever return a result I'd suggest running the calls separately, ie:

Code:
``=LOOKUP(9.99E+307,CHOOSE({1,2,3,4},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","002","Month",2),GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","003","Month",2),GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","005","Month",2)))``

to reiterate though - if you need to aggregate at any time then the above is not viable.

Last edited:

#### dem86

##### New Member
Hi and thanks for the quick reply.

The situation I have is that I could have 3, 2, 1 or none that return #Ref.

I was using If(iserror but my formula was too long which is why I was recommended to use the method I posted here.

I guess it is back to the drawing board.

Doug

#### DonkeyOte

##### MrExcel MVP
dem86 said:
I was using If(iserror but my formula was too long which is why I was recommended to use the method I posted here.

Yes I think a recall a post along these lines previously (elsewhere)

Regards point a) from my prior post (given option b is precluded)

Code:
``=LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","002","Month",2)))+LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","003","Month",2)))+LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA("Total_Cost",'Summary Financial'!\$A\$3,"Req_project_code","11-005","Time_step_num","005","Month",2)))``

Longer than before but still shorter syntax wise than using double evaluation - we're assuming of course you're required to use with versions prior to XL2007 - if not use IFERROR rather than the LOOKUP construct above.

#### dem86

##### New Member
unfortunately i am still on 2003.. but this work great...

Thanks again!

Replies
8
Views
2K
Replies
0
Views
490
Replies
12
Views
584
Replies
6
Views
264
Replies
1
Views
282

1,171,429
Messages
5,875,491
Members
433,131
Latest member
ThatOneDude

### 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