Page 1 of 1

[Solved] Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 7:07 am
by Anagoge
I have a spreadsheet like this, where the number of rows per month is variable:

Code: Select all

   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)

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 8:45 am
by Nikos
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

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 10:19 am
by keme
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

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

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.

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 1:40 pm
by Nikos
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

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 2:31 pm
by keme
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.

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 9:01 pm
by Anagoge
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.)

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 9:36 pm
by Anagoge
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.

Re: Sum Based on Dynamic Date Range Condition

Posted: Tue Sep 16, 2008 10:53 pm
by Villeroy
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

Re: Sum Based on Dynamic Date Range Condition

Posted: Wed Sep 17, 2008 8:35 am
by Nikos
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.

Re: Sum Based on Dynamic Date Range Condition

Posted: Wed Sep 17, 2008 1:22 pm
by Villeroy
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.

Re: Sum Based on Dynamic Date Range Condition

Posted: Wed Sep 17, 2008 5:16 pm
by Anagoge
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.