How do I sum a Data range through a cutoff date ?
I thought I had submitted a common problem but got no response.
..
I have a varying CutOff Date in cell B1 currently equal to 8/23 (after
formatting).
The calendar starts in C2, last Column unknown.
The Data itself starts in Row 3, Last row unknown.
..
I want to loop through each row and fill in the column B
the sum of the range from Column C through the Cutoff Date Column.
..
The expected result in Column B (Done)
will be 80 for Item Y and 400 for Item Z.
..
Cut off Date : 8/23
Item Done 8/21 8/22 8/23 8/24 8/25 ...
Y 80 60 20 40
Z 400 140 100 160 120 180 ...

....
....
Can you help me ?
Thank you,
Celeste

What you have is essentially a sum for a range that will chage dynamically
based on the cuttoff date.
I can't think of any way to do this other than doing a user defined
function... here is my crack at it.

Public Function SumBeforeCutOff(RangeToSum As Range, DateRange As Range,
CutOffDate As Date) As Double
'Like vLookUp function, this assumes that RangeToSum and DateRange are
the same size and are aligned, and in this case are both only 1 row high
Dim c As Range
Dim DateRow As Long
Dim RangeRow As Long
Dim DateRowOffset As Long

Application.Volatile

DateRow = DateRange.Row
RangeRow = RangeToSum.Row
DateRowOffset = DateRow - RangeRow

For Each c In RangeToSum
If c.Offset(DateRowOffset) <= CutOffDate Then
SumBeforeCutOff = SumBeforeCutOff + c
End If
Next

End Function

This needs to be put in a code module (not an excel object)
Then, to use your example, the cell where the sum needs to be would have a
formula sortof like this: =SumBeforeCutOff(C3:F3,C$2:F$2,B$1) or if you want
to use all possible columns =SumBeforeCutOff(C3:IV3,C$2:IV$2,B$1)

This seem to work for you particular example... but there is no error
checking and all dates must be actal date values.

Also, it won't care if the dates are in order... so in fact, you could add
another column at the end, and if the date in the header in before the cutoff
date, the value will be included in the sum.

Hope this helps.

"u473" wrote:

How do I sum a Data range through a cutoff date ?
I thought I had submitted a common problem but got no response.
..
I have a varying CutOff Date in cell B1 currently equal to 8/23 (after
formatting).
The calendar starts in C2, last Column unknown.
The Data itself starts in Row 3, Last row unknown.
..
I want to loop through each row and fill in the column B
the sum of the range from Column C through the Cutoff Date Column.
..
The expected result in Column B (Done)
will be 80 for Item Y and 400 for Item Z.
..
Cut off Date : 8/23
Item Done 8/21 8/22 8/23 8/24 8/25 ...
Y 80 60 20 40
Z 400 140 100 160 120 180 ...

....
....
Can you help me ?
Thank you,
Celeste