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

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

Sort by » oldest newest most voted

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.

more

Libreoffice 4.3.7 and Windows 7.

=SUM(IF(MONTH(A1:A9)={5,1,3},B1:B9,0))

=SUM(IF((MONTH(A1:A9)=5)+(MONTH(A1:A9)=1)+(MONTH(A1:A9)=3),B1:B9,0))

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

more

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.
SumForSelectedMonths_ArrayFunction.ods

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.

( 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?.

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

( 2015-09-07 23:49:45 +0200 )edit