## [Solved] Sum Based on Dynamic Date Range Condition

### [Solved] Sum Based on Dynamic Date Range Condition

I have a spreadsheet like this, where the number of rows per month is variable:
Code: Select all   Expand viewCollapse view
`   A          B     C1  Date       Qty   MonthlyTotal2  1/1/2008   1    3  1/12/2008  3 4  1/22/2008  2     65  2/1/2008   5    6  2/18/2008  4     97  3/28/2008  38  3/28/2008  1     4`

I need to write a formula to calculate the monthly sum (into Column C) of the Qty on the last day row present for that month. The below formula almost works, but I don't know how to make the Sum() operation dynamically look for the range of cells so it only adds up that month's Qty values (i.e. the B2 reference part needs to be dynamically changed to match that month's first row):
C4=IF(MONTH(A4)<>MONTH(A5); SUM(B2:B4); "")

(For simplicity, ignore the fact that the last row will always have a sum)
Last edited by Anagoge on Wed Sep 17, 2008 5:17 pm, edited 1 time in total.
Anagoge

Posts: 17
Joined: Mon Sep 15, 2008 9:19 pm

### Re: Sum Based on Dynamic Date Range Condition

I have one suggestion, but it requires an extra column. Maybe you (or somebody else) will fit it in one formula and be so kind posting it here.

C4=Month(A4)
D4=IF(MONTH(A4)<>MONTH(A5);SUMIF(C\$2:\$C\$8;MONTH(A4);\$B\$2:\$B\$8);"")

Of course you would want to use defined ranges instead of C\$2:\$C\$8 or \$B\$2:\$B\$8.

Greetings

Nikos
LibreOffice 4.0.5 on OpenSuse 12.3/64bit/KDE4.10 (Version from the TDF page) + LibreOffice 4.1.1 on Debian Wheezy/64bit/KDE4.8 + LibreOffice 4.1.1 on Win7/64bit
Nikos

Posts: 172
Joined: Mon Dec 17, 2007 11:50 am

### Re: Sum Based on Dynamic Date Range Condition

The month ranges will be varying in size, so you need a way to determine the range by calculation. I suggest using two additional columns, one for the grouping criterion and one for the range limits:
Code: Select all   Expand viewCollapse view
`D1: MonthE1: range startD2: =MONTH(A2)E2: =MATCH(D2;\$D\$2:\$D\$10;0)`
Now as you suggested, detect a month change and make a sum of the range above:
Code: Select all   Expand viewCollapse view
`C2: =IF(D3=D2;"";SUM(OFFSET(\$B\$2:\$B\$11;E2-1;0;E3-E2;1)))`

The above requires that the list is for one year only (new list per year), and that it's sorted by date. If you cross a year boundary, you must check for year too, which makes it slightly more complicated.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2536
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Sum Based on Dynamic Date Range Condition

Here is an alternative formula that needs no extra columns:

C4=IF(MONTH(A4)<>MONTH(A5);SUMPRODUCT(MONTH(A\$2:\$A\$700)=MONTH(A4);\$B\$2:\$B\$700);"")

The month ranges will be varying in size, so you need a way to determine the range by calculation

I don't think this will be necessary, if you use a formula that applies a condition to sum-up your values, and you define a sufficiently large range. That's why I suggest you use range names.

The above requires that the list is for one year only (new list per year), and that it's sorted by date

same requirements here.

Hope this helped

Nikos
LibreOffice 4.0.5 on OpenSuse 12.3/64bit/KDE4.10 (Version from the TDF page) + LibreOffice 4.1.1 on Debian Wheezy/64bit/KDE4.8 + LibreOffice 4.1.1 on Win7/64bit
Nikos

Posts: 172
Joined: Mon Dec 17, 2007 11:50 am

### Re: Sum Based on Dynamic Date Range Condition

That's correct. Nikos' solution (using either SUMPRODUCT() or SUMIF()) will do the job in a more compact manner. However, it looks at the entire range, so it is slightly more vulnerable to "stray numbers" if you define large ranges to accomodate future input, and then later use the "space down below" for other things, but this problem is not very likely to arise.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2536
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Sum Based on Dynamic Date Range Condition

Thanks for all of your ideas/examples. I tried the SumProduct solution:

=IF(MONTH(A4)<>MONTH(A5);SUMPRODUCT(MONTH(A\$2:\$A\$700)=MONTH(A4);\$B\$2:\$B\$700);"")

and this works well if you have a single calendar year of data. But sadly, I have multiple years that get summed together (since Month(Jan 1, 2008) = Month(Jan 1, 2007)), I tried using EOMonth (end of month) to capture/compare both the year and month at the same time like this:
=IF(EOMONTH(A4;0)<>EOMONTH(A5;0); SUMPRODUCT(EOMONTH(A\$2:A\$700;0)=EOMONTH(A4;0); \$B\$2:\$B\$700); "")

But the EOMONTH(A\$2:A\$700;0) part results in this error: #VALUE!
I don't understand why Month would work there, but not EOMonth. EOMonth seems fine the other three places it is used above without cell ranges.

So I instead tried to manually match both the Month and Year like this:

IF(MONTH(A4)<>MONTH(A5);SUMPRODUCT(AND(MONTH(A\$2:A\$700)=MONTH(A4);(YEAR(A\$2:A\$700)=YEAR(A4)));\$B\$2:\$B\$700);"")

But that results in the same type of error: #VALUE! on the AND statement.

(It would be very nice if the function wizard dialog was resizable and allowed temporary linefeeds to separate/indent parts of the function. Editing longer formulas there is difficult.)
Anagoge

Posts: 17
Joined: Mon Sep 15, 2008 9:19 pm

### Re: Sum Based on Dynamic Date Range Condition

I also just tried a variation using the suggested SUMIF function:

=IF(MONTH(A6)<>MONTH(A7);SUMIF(\$A\$2:\$A\$700;MONTH(A\$2:A\$700)=MONTH(A6);\$B\$2:\$B\$700);"")

The SUMIF function is always returning 0 for my test.

I think the problem with the SumProduct attempt above that combines the Month/Year test is that the AND does not return an array, but I don't know how to get an array out of the combined Month/Year condition.
Anagoge

Posts: 17
Joined: Mon Sep 15, 2008 9:19 pm

### Re: Sum Based on Dynamic Date Range Condition

The SUMPRODUCT formula is plain wrong. It can not work with an empty string.
http://wiki.services.openoffice.org/wik ... T_function
SUMPRODUCT(MONTH(A\$2:\$A\$700)=MONTH(\$A\$4);YEAR(A\$2:\$A\$700)=YEAR(A4);\$B\$2:\$B\$700) returns the sum in B where the month in A is the same month as in A4 and the year in A is the same year as in A4.
Now do the following in 3 unused columns:
X2:X700: =MONTH(A\$2:\$A\$700)=MONTH(\$A\$4) [Ctrl+Shift+Enter]
Y2:Y700: =YEAR(A\$2:\$A\$700)=YEAR(A4) [Ctrl+Shift+Enter]
Z2:Z700: =Y2:Y700*X2:X700*B2:B700 [Ctrl+Shift+Enter]
The sum of the products in Z2:Z700 gives the same result as the above suggested SUMPRODUCT
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy
Volunteer

Posts: 23955
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Sum Based on Dynamic Date Range Condition

Here is the SUMPRODUCT function taking account of the year as suggested by Villeroy
=IF(MONTH(A4)<>MONTH(A5);SUMPRODUCT(MONTH(A\$2:\$A\$700)=MONTH(A4);\$B\$2:\$B\$700;YEAR(A\$2:A\$700)=YEAR(A4));"")

It can not work with an empty string.

Probably I missed something, but where is the empty string?

The sum of the products in Z2:Z700 gives the same result as the above suggested SUMPRODUCT

Yes, but only if the "IF test" is omitted.

PS:@Villeroy: I know I can be a pain in the ass, but hopefully no hard feelings.
LibreOffice 4.0.5 on OpenSuse 12.3/64bit/KDE4.10 (Version from the TDF page) + LibreOffice 4.1.1 on Debian Wheezy/64bit/KDE4.8 + LibreOffice 4.1.1 on Win7/64bit
Nikos

Posts: 172
Joined: Mon Dec 17, 2007 11:50 am

### Re: Sum Based on Dynamic Date Range Condition

Nikos wrote:Here is the SUMPRODUCT function taking account of the year as suggested by Villeroy
=IF(MONTH(A4)<>MONTH(A5);SUMPRODUCT(MONTH(A\$2:\$A\$700)=MONTH(A4);\$B\$2:\$B\$700;YEAR(A\$2:A\$700)=YEAR(A4));"")

It can not work with an empty string.

Probably I missed something, but where is the empty string?

That was my failure. I read it as last argument of SUMPRODUCT
The sum of the products in Z2:Z700 gives the same result as the above suggested SUMPRODUCT

Yes, but only if the "IF test" is omitted.

PS:@Villeroy: I know I can be a pain in the ass, but hopefully no hard feelings.

I always omit "IF tests" like that one since they tend to obfuscate the core of the problem.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy
Volunteer

Posts: 23955
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Sum Based on Dynamic Date Range Condition

I ended up using SumProduct and added a bit of logical IF/AND/OR complication to handle dates at the end of the list slightly better:

=IF(OR(AND(MONTH(\$A4)<>MONTH(\$A5);NOT(ISBLANK(\$A5)));EOMONTH(\$A4;0)=MONTH(\$A4));SUMPRODUCT(MONTH(\$A\$2:\$A\$700)=MONTH(\$A4);YEAR(\$A\$2:\$A\$700)=YEAR(\$A4);\$B\$2:\$B\$700);"")

I now understand why the year check needs to be a separate parameter to SumProduct. Thanks for all of your help! Everyone here has been very helpful as I learn more about Calc formulas.
Anagoge

Posts: 17
Joined: Mon Sep 15, 2008 9:19 pm