Ask Your Question

# 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

## 3 Answers

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.

Please try the following formulas.

=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

## Comments

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.

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

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

## Stats

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

Seen: 4,195 times

Last updated: Aug 31 '15