How to sum by year and month from external source

[MaximusDecimus]

I have one file where I have consolidated by year and month like this

Local file in work

YEAR MONTH  SUM_PRICE
2013     1
2013     2
2013     3
2013     4
2013     5

While in another file I have the details like this

External file is Sales.ods

DATE           YEAR(fx)      MONTH(fx) PRICE
2013-01-15     2013              1       3.5
2013-01-20     2013              1       4.5
2013-01-22     2013              1       6.5
2013-01-23     2013              1       1.5
2013-02-15     2013              2       2.3
2013-02-20     2013              2       3.4
2013-03-11     2013              3       234.5
2013-04-05     2013              4       35.0
2013-05-14     2013              5       3.99
2013-05-23     2013              5       3.5

I want to get the sum per year and month, but I don’t know how to perform this

I have been trying this formula in my local file to get sum from external file, where A is the date and D is the price

=IF(AND(YEAR(‘file:///D:/files/Sales.ods’#$Sales.$A$2:$A$103) = A3,MONTH(‘file:///D:/files/Sales.ods’#$Sales.$A$2:$A$103) =B3),‘file:///D:/files/Sales.ods’#$Sales.$D$2:$D$103,0)

I just get the first value that matches then the rest are zeros.

Then I tried (Remember D is the price from the detailed external file, B is the extracted year, C is the extracted month A2 and B2 are the current cells from local file)

=SUMIFS(‘file:///D:/files/Sales.ods’#$Sales.$D$2:$D$103, ‘file:///D:/files/Sales.ods’#$Sales.$B$2:$B$103, A2, ‘file:///D:/files/Sales.ods’#$Sales.$C$2:$C$103, B2)

With this I get a Err504

Can this be achieved with another function or am I closer with my functions?

[robleyd]

Is there any reason why you cannot use a pivot table in Sales.ods? Creating Pivot Tables - LibreOffice Help

[MaximusDecimus]

Good point. The way I asked it is not clear enough. In fact, the local file contains more columns with other data calculated that is not a pivot table (even if in the question the local file look likes a pivot table)
and I just want to summarize the price from external file in that column

[MaximusDecimus]

In my external file I originally created a pivot table and I wanted just to compare easily year and month and get the sum price, but anytime I refreshed my pivot table I have an issue with the year. If you can look at my another question How to repeat row labels in a pivot table and tell me how to achieve that, that would be awesome. Thanks.

[Lupp]

See bug tdf#94309.
As the data accessed by references into remote sources are drawn in (to hidden tables) and stored with the evaluating document anyway, I would advise to do an explicit import into a dedicated sheet, and to redirect the external references thereto to get internal ones. Shorter and clearer formulae, more secure and better verifyablke working, no disadvantages. The simple access (by array-formulae probably) will work - and if a bug arises it will be set for fixing on a high priority. Avoid playing tricks in the dark.

Unsolicited advice:
Data accessed in a document by external reference are pulled into the target document anyway (into hidden tables). If you do the import by explicit means instead of leaving it in the dark, you have better control and most likely this proceeding will be less error-prone (e.g. due to unfixed bugs). In addition you avoid the multiple automatic import of (partly) identical external data that are referenced in different places of the target document.
Expand the two files contained in this attached .zip into the same folder and test yourself. I had to append a fake extension because this site does not accept .zip files for upload. Remove the lying .odg and replace it by .zip.
(Best practice is to avoid external reference wherever possible at all.)