# 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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,987
Messages
5,856,664
Members
431,827
Latest member
dbonser

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