Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

continutation.

this time, i need to count the number of cells AFTER the sum of the prior 12 has reached a threshold. last time it was just counting cells after a single cell hit a threshold.

so once the prior 12 cells sum is greater than 232,000, then i need to count the number of subsequent cells with data in them.

https://www.experts-exchange.com/questions/29115304/Count-columns-after-hitting-a-threshold.html

this time, i need to count the number of cells AFTER the sum of the prior 12 has reached a threshold. last time it was just counting cells after a single cell hit a threshold.

so once the prior 12 cells sum is greater than 232,000, then i need to count the number of subsequent cells with data in them.

https://www.experts-exchange.com/questions/29115304/Count-columns-after-hitting-a-threshold.html

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial```
=RunningSum(H1:CA1,12,12,215000,2)
=CountAfterRunningSum(H1:CA1,12,0,215000,2)
```

The revised code is shown below:```
Function RunningSum(rgToSum As Range, LookBack As Long, LookAhead As Long, TargetValue As Double, Optional IncludePivotCell As Long = 2) As Variant
'Calculates the various sums of LookBack number of values in rgToSum. When one of those sums exceeds TargetValue, then returns sum of LookAhead number of cells.
'If IncludePivotCell is 0, it is not included in either the looking back sum or looking forward sum.
'If IncludePivotCell is 1, it is included in both the looking back sum and looking forward sum
'If IncludePivotCell is 2, it is included in the looking back sum but not the looking forward sum
Dim cel As Range, Finish As Range, rgBack As Range, rgAhead As Range, Start As Range
Dim dLookBack As Double, dLookAhead As Double
Dim StartAddr As String, FinishAddr As String
Dim i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
StartAddr = Start.Address
FinishAddr = Finish.Address
RunningSum = "Failed"
For Each cel In rgToSum.Cells
Set rgBack = Nothing
Set rgAhead = Nothing
dLookBack = 0
dLookAhead = 0
Select Case IncludePivotCell
Case 0
If Not cel.Address = StartAddr Then Set rgBack = cel.Offset(0, -1)
If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
Case 1
Set rgBack = cel
Set rgAhead = cel
Case 2
Set rgBack = cel
If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
End Select
If Not rgBack Is Nothing Then
i = Application.Max(Start.Column, rgBack.Column - LookBack + 1)
Set rgBack = Range(rgBack, rgBack.EntireRow.Cells(1, i))
dLookBack = Application.Sum(rgBack)
End If
If Not rgAhead Is Nothing Then
j = Application.Min(Finish.Column, rgAhead.Column + LookAhead - 1)
Set rgAhead = Range(rgAhead, rgAhead.EntireRow.Cells(1, j))
dLookAhead = Application.Sum(rgAhead)
End If
If dLookBack >= TargetValue Then
RunningSum = dLookAhead
Exit Function
End If
Next
End Function
Function CountAfterRunningSum(rgToSum As Range, LookBack As Long, LookAhead As Long, TargetValue As Double, Optional IncludePivotCell As Long = 2) As Variant
'Calculates the various sums of LookBack number of values in rgToSum. When one of those sums exceeds TargetValue, then returns number of LookAhead columns with non-zero values.
'If LookAhead is 0, then all remaining columns in rgToSum are included in the count
'If IncludePivotCell is 0, it is not included in either the looking back sum or looking forward count.
'If IncludePivotCell is 1, it is included in both the looking back sum and looking forward count
'If IncludePivotCell is 2, it is included in the looking back sum but not the looking forward count
Dim cel As Range, Finish As Range, rgBack As Range, rgAhead As Range, Start As Range
Dim dLookBack As Double, dLookAhead As Double
Dim StartAddr As String, FinishAddr As String
Dim i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
StartAddr = Start.Address
FinishAddr = Finish.Address
CountAfterRunningSum = "Failed"
For Each cel In rgToSum.Cells
Set rgBack = Nothing
Set rgAhead = Nothing
dLookBack = 0
dLookAhead = 0
Select Case IncludePivotCell
Case 0
If Not cel.Address = StartAddr Then Set rgBack = cel.Offset(0, -1)
If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
Case 1
Set rgBack = cel
Set rgAhead = cel
Case 2
Set rgBack = cel
If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
End Select
If Not rgBack Is Nothing Then
i = Application.Max(Start.Column, rgBack.Column - LookBack + 1)
Set rgBack = Range(rgBack, rgBack.EntireRow.Cells(1, i))
dLookBack = Application.Sum(rgBack)
End If
If Not rgAhead Is Nothing Then
j = IIf(LookAhead = 0, Finish.Column, Application.Min(Finish.Column, rgAhead.Column + LookAhead - 1))
Set rgAhead = Range(rgAhead, rgAhead.EntireRow.Cells(1, j))
dLookAhead = Application.CountIf(rgAhead, "<>0")
End If
If dLookBack >= TargetValue Then
CountAfterRunningSum = dLookAhead
Exit Function
End If
Next
End Function
```

RunningSumTestingQ29115305-SUMMING-.xlsm
Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

Open in new window

The code is:

Open in new window

RunningSumTestingQ29115305.xlsm