GetPivotData multiple data multiple filter items

[Sachin Bafna]

Hi,

I am trying of get sum of values from a pivot table using getpivotdata function. The requirement is to get the sum based on certain values. My financial year is Jan to Aug. The formula used is

=SUM(GETPIVOTDATA("Value",$A$3,"Sub Region","Australia","Date",{1,2,3,4,5,6,7,8},"Years",2016),GETPIVOTDATA("Value",$A$3,"Sub Region","Australia","Years",{2014, 2015}))

In the above formula 1 to 8 are the months Jan to Aug. This function works fine when all the months are available in the pivot table.
But the moment I change the filter to another group, the underlying data changes as there might not be all the months from Jan to Aug in that group. This results in the formula to error as #REF.

Please help such that it ignores the data points (months in this case) which are not present and provides the sum for the available months.

I have attached a sample file for ease of understanding and correcting the formula.

Thanks,
Sachin

 

[Nebu]

Hi:

May be something like this?

Thanks

 

[Sachin Bafna]

Thanks Nebu. Thats fantastic. I think this works great. Two questions:

1. Can I have the pivot dates in the format like grouped by Year and Month, so that I am able to view the summary pivot data as well when necessary? My original spreadsheet is having the values group by Year and month, but somehow the sample that I created has the dates alone. I would actually prefer in this format.

2. How do I change the date format in my pivot table, such that it becomes DD-MM-YYYY format. Currently its still grouped by Year, Quarter and the date field itself.

Thanks once again.

regards,
Sachin