Conditional Sum....is this possible?

[NewStranger]

Good evening, All....I have searched the KB, and haven't found anything quite like this.....

I am attempting to make a spreadsheet that will total the daily hours and give an accumulated value for the past 7 days, on a "rolling 7 day basis"

Where I am running into trouble, is when I try to allow for a condition "reset" and have the formula recalculate from "0", or more properly...from the "reset" point....

Any help, suggestions, direction to look for a solution; would be greatly appreciated....

I have attached the worksheet which will hopefully help explain what I am trying to do....

Many thanx.....

[malik641]

Howdy NewStranger, welcome to the forums :hi:

I'm not sure if this is what you're trying to do..but try this out:

Option Explicit
Option Compare Text

Public Function Sum7Days(ByVal rng As Range) As Variant
' This function will only handle single cell ranges
On Error Resume Next

Dim TopCell As Range
Dim i As Long
Dim Total As Single

If rng.Rows.Count > 1 Then
Sum7Days = CVErr(xlErrValue)
Exit Function
End If

If rng.Offset(-1, 0).Value = "" Then
Set TopCell = rng
Else
Set TopCell = rng.End(xlUp)
End If

For i = rng.Row To TopCell.Row Step -1
If Cells(i, rng.Offset(0, -1).Column).Value = "reset" Then
Sum7Days = Total
Exit Function
End If
Total = Total + Cells(i, rng.Column).Value
Next

Sum7Days = Total
Application.Volatile True
End Function

In a cell you would enter (using your example) the cell to the left's range for the range e.g. in cell D4 you would have "=Sum7Days(C4)" for the formula and copy all the way down. The function only accepts one cell...I think this is what you're looking for.

I'm not sure how to do this with formulas, though.

Check out the example. :)

[mdmackillop]

Not totally clear either, but try this in cell D10 and copy down
=IF(B10="reset", "0.00",SUM(OFFSET(C10,-7,0,7,1)))