GETPIVOTDATA #REF error.

[TinaMHa]

Good morning,

I am using the following and it keeps giving me a #REF when one of the items is not located in the pivot table for that particular location.  What can I do to still get the other locations data without typing over the formula?

=F8*(GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS","Location","Bolivar")+GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS","Location","Bolivar"))

Any help would be greatly appreciated.

[#2]

@TinaMHa 

You may enter locations in some cells, e.g. M8, M9, etc. If in M8 is Bolivar the formula could be

=F8*(
GETPIVOTDATA(
    "Sum of Quantity",
    $A$3,
    "Charge",
    "BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS",
    "Location",$M8) +
GETPIVOTDATA("Sum of Quantity",
    $A$3,
    "Charge",
    "BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS",
    "Location",
    $M8)
)

and drag it down. But all depends on how your data is structured.

[#3]

@Sergei Baklan 

An Example of the pivot is below:

[#4]

@Sergei Baklan 

I figured it out:

=F8*(IFERROR(GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS","Location","Bolivar"),0)+(IFERROR(GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS","Location","Bolivar"),0)))

[#5]

@TinaMHa 

Thank you. Under Bolivar you have no data for the field 

"BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS"

thus GETPIVOTDATA returns an error if you reference above field.

As variant you may wrap it by IFERROR

=F8*(
GETPIVOTDATA(
    "Sum of Quantity",
    $A$3,
    "Charge",
    "BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS",
    "Location",$M8) +
IFERROR(
GETPIVOTDATA("Sum of Quantity",
    $A$3,
    "Charge",
    "BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS",
    "Location",
    $M8), 0 )
)

Where to keep locations depends on where result of the formula to be placed. In general if build PivotTable on data model and if you are on Excel 365 that could be returned the spill for all locations using CUBE formulae.

[#6]

@TinaMHa 

Yes, thank you for the update