# How to sum by year and month from external source

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?

edit retag close merge delete

Is there any reason why you cannot use a pivot table in Sales.ods? https://help.libreoffice.org/Calc/Cre...

( 2017-08-03 05:21:41 +0100 )edit

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

( 2017-08-03 05:28:01 +0100 )edit

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 https://ask.libreoffice.org/en/questi... and tell me how to achieve that, that would be awesome. Thanks.

( 2017-08-03 05:31:25 +0100 )edit

Sort by » oldest newest most voted

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.