Problem with getpivotdata statement

[Choochoo22]

I’m trying to migrate a rather complicated utility tracking spreadsheet from Excel to Calc. Most of it seems to work OK as-is but one set of statements fail and errors propagate from there through the spreadsheet. The following statement is intended to extract the total of KWH used in my car each month from a pivot table containing totals by month for several years. It works in Excel but in Calc it produces a blank cell or a #ref error if the IFERROR function is removed.

=IFERROR(GETPIVOTDATA("KWH",$'Prius Fuel'.$R$3,"Date",MONTH(A12),"Years",YEAR(A12)),"")

If the conditions are removed the statement extracts the KWH total from the table. Adding conditions, however, cause the statement to fail no matter how I’ve tried altering the punctuation or using fixed conditions instead of MONTH and YEAR.

Attached are screenshots showing the pivot table and the GETPIVOTDATA results as they appear in both Calc and Excel. It probably doesn’t matter and I don’t care which way it looks but the table shows as two columns in Excel and three in Calc. The #VALUE errors that show up in columns G & H are because of the missing pivot table data.

Rick

Pivot table in Excel.jpg
Pivot table in Calc.jpg
GETPIVOTDATA in Excel.jpg
GETPIVOTDATA in Calc.jpg

[mariosv]

Can you edit your question and attach a sample file?, I think it is about how dates are managed on PT.

[mariosv]

Please try

=IFERROR(GETPIVOTDATA("KWH",$'Prius Fuel'.$R$3,"Month",TEXT(A12,"MMM"),"Years",YEAR(A12)),"")

Libreoffice search for the showed data not into the original date value.

[Choochoo22]

I am now blessed with two solutions. Thank you m.a.risov for this solution:

=IFERROR(GETPIVOTDATA("KWH",$'Prius Fuel'.$R$3,"Month",TEXT(A12,"MMM"),"Years",YEAR(A12)),"")

And thank you johnSUN for this solution:

=IF($A12>TODAY(),"",SUMPRODUCT($'Prius Fuel'.$I$7:$I$500,YEAR($'Prius Fuel'.$A$7:$A$500)=YEAR($A12),MONTH($'Prius Fuel'.$A$7:$A$500)=MONTH($A12)))

Both solutions solve the problem. The first is the most direct answer to the question of extracting the data from the pivot table. The second extracts the data from the original range without the need for a pivot table. (In both cases the “IF’s” are just to keep the fields blank for future lines where no data has yet been entered.)