[Solved] Sum Based on Dynamic Date Range Condition

Discuss the spreadsheet application

[Solved] Sum Based on Dynamic Date Range Condition

Postby Anagoge » Tue Sep 16, 2008 7:07 am

I have a spreadsheet like this, where the number of rows per month is variable:
Code: Select all   Expand viewCollapse view
   A          B     C
1  Date       Qty   MonthlyTotal
2  1/1/2008   1   
3  1/12/2008  3
4  1/22/2008  2     6
5  2/1/2008   5   
6  2/18/2008  4     9
7  3/28/2008  3
8  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

Postby Nikos » Tue Sep 16, 2008 8:45 am

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

Postby keme » Tue Sep 16, 2008 10:19 am

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: Month
E1: range start
D2: =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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Sum Based on Dynamic Date Range Condition

Postby Nikos » Tue Sep 16, 2008 1:40 pm

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

Postby keme » Tue Sep 16, 2008 2:31 pm

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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Sum Based on Dynamic Date Range Condition

Postby Anagoge » Tue Sep 16, 2008 9:01 pm

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

Postby Anagoge » Tue Sep 16, 2008 9:36 pm

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

Postby Villeroy » Tue Sep 16, 2008 10:53 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sum Based on Dynamic Date Range Condition

Postby Nikos » Wed Sep 17, 2008 8:35 am

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

Postby Villeroy » Wed Sep 17, 2008 1:22 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sum Based on Dynamic Date Range Condition

Postby Anagoge » Wed Sep 17, 2008 5:16 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests