getpivotdata sum 1 item

[postalservices]

Hi,

The following formula is returning #REF! and I do not know what I am doing wrong.

=IFERROR(SUM(GETPIVOTDATA("Volume",'17x17 Financials'!$A$50,"Origin (17)","Toronto","Destination (17)",{"St. John'',"Halifax",Moncton","Saint John","Quebec","Montreal","Ottawa","Toronto","Kitchener",Winnipeg","Regina","Saskatoon","Calgary","Edmonton","Vancouver","Victoria","Airstage"},"Urban/Rural","Airstage")),0)

HELP please!

Thanks

 

[Eloise T]

Is the syntax and specified location accurate? i.e. '17x17 Financials'!$A$50

Did you use Control+Shift+Enter when you entered the formula since it contains an array?

You get the Excel #REF! error when the Pivot Table doesn't show the totals for one of your items. Check [field2, item2].

 

[NARAYANK991]

Hi ,

I am not sure you have directly copied and pasted the formula , but it is missing two double quotes shown in red :

=IFERROR(SUM(GETPIVOTDATA("Volume",'17x17 Financials'!$A$50,"Origin (17)","Toronto","Destination (17)",{"St. John'',"Halifax","Moncton","Saint John","Quebec","Montreal","Ottawa","Toronto","Kitchener","Winnipeg","Regina","Saskatoon","Calgary","Edmonton","Vancouver","Victoria","Airstage"},"Urban/Rural","Airstage")),0)

Whether this is the problem is also difficult to say ; try putting them in and let us know.

Narayan

 

[Eloise T]

Additionally, I'm not getting a value_if_error on IFERROR.

 

[postalservices]

Thanks!

But it is returning 0 when it should be returning a value.