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
 

Some videos you may like

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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Mar 16, 2011
Messages
3
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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Mar 16, 2011
Messages
3
unfortunately i am still on 2003.. but this work great...

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,124
Messages
5,599,839
Members
414,342
Latest member
K Darrell Smith

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