Ask Your Question

How to sum by year and month from external source

asked 2017-08-03 05:14:50 +0200

MaximusDecimus gravatar image

updated 2017-08-03 05:15:22 +0200

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

Local file in work

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 flag offensive close merge delete


Is there any reason why you cannot use a pivot table in Sales.ods?

robleyd gravatar imagerobleyd ( 2017-08-03 05:21:41 +0200 )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

MaximusDecimus gravatar imageMaximusDecimus ( 2017-08-03 05:28:01 +0200 )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 and tell me how to achieve that, that would be awesome. Thanks.

MaximusDecimus gravatar imageMaximusDecimus ( 2017-08-03 05:31:25 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-08-03 12:20:02 +0200

Lupp gravatar image

updated 2017-08-03 13:14:49 +0200

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.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-08-03 05:14:50 +0200

Seen: 284 times

Last updated: Aug 03 '17