Help with formula adding pivot data

dem86

New Member
Joined
Mar 16, 2011
Messages
3
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,222,018
Messages
6,163,427
Members
451,836
Latest member
boxboxbox

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