Ask Your Question

How do I conditionally calculate a sum of values based on a date range?

asked 2015-08-11 21:25:43 +0200

agronholm gravatar image

I have two columns, A and C (with B in between them). A contains dates and C contains currency values. I would like to calculate a sum of all C values where the month of the corresponding A value falls within a given range. How do I do this?

My best attempt is this: SUM(IF(MONTH(A1:A9999)={3; 4; 5}; C1:C9999; 0)).

This returns always 0, however. The cause seems to be that the MONTH() function always returns 12 for any date values when given an array as input. DATE(A1:A2) gives 12 and 12. 1 and 1 would be correct with my data. If I include 12 in the array of values, it sums the values of all rows!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-08-26 15:23:53 +0200

erAck gravatar image

Important is that you enter your formula as array/matrix formula using Shift+Ctrl+Enter instead of just Enter, otherwise an implicit position dependent intersection of the formula cell with the A1:A9999 range is passed to the IF() conditional expression and only the first result of the comparison of that value with {3;4;5} is the condition. Using that formula for example in D10000 will give a #VALUE! result if not entered as array/matrix formula.

edit flag offensive delete link more

answered 2015-08-25 03:21:26 +0200

lolax gravatar image

Libreoffice 4.3.7 and Windows 7.

Please try the following formulas.



And please make sure to press CTRL+SHIFT+ENTER after typing the formula.

edit flag offensive delete link more

answered 2015-08-27 01:08:15 +0200

m.a.riosv gravatar image

updated 2015-08-31 21:55:15 +0200

Edited 20150831 Corrected initial explanation it was wrong, because I was using row separator in the formulas instead column separator.

Deleted the image, could create confusion with the used separators

Updated attached sample, with several solutions.

edit flag offensive delete link more


Make sure you use the array column separator, which may be ',' comma instead in your configuration, to produce a row vector. What happens with MONTH(A1:A9999)={3,4,5} in array context is that internally a 9999 rows x 3 columns matrix is produced with boolean values for each comparison.

erAck gravatar imageerAck ( 2015-08-31 15:41:00 +0200 )edit

Thanks for hint @erAck, pls forgive my mistake, I have the separators: F ";" A-C "|" A-R ";" because default F ";" A-C "." A-R ";" it's a bit confusing. Using the column separator it's how it works for me {=SUM(IF(MONTH(A1:A9999)={3|4|5};C1:C9999;0))}, OTH =SUMPRODUCT((MONTH(A1:A9999)={3|4|5})C1:C9999)* works but =SUMPRODUCT(MONTH(A1:A9999)={3|4|5};C1:C9999) not, is it because in this second there are different array size generated for both parameters?.

m.a.riosv gravatar imagem.a.riosv ( 2015-08-31 21:41:17 +0200 )edit

Yes, SUMPRODUCT multiplies the corresponding elements of the given arrays, so all arrays must be of the same dimensions and size.

erAck gravatar imageerAck ( 2015-09-07 23:49:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-08-11 21:24:49 +0200

Seen: 4,193 times

Last updated: Aug 31 '15