# Count columns after hitting a SUMMING threshold

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
byundt

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
finnstone

thanks!

the count seems to be short by 1 value every time, i.e., 3 instead of 4?
I revised the code in both user defined functions so you would have the option to include the pivot cell in the lookback sum, but not the range of cells looking ahead. To invoke that option, use 2 as the last parameter in each call.
``````=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 i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
RunningSum = "Failed"

For Each cel In rgToSum.Cells
Set rgBack = Nothing
dLookBack = 0

Select Case IncludePivotCell
Case 0
Case 1
Set rgBack = cel
Case 2
Set rgBack = cel
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
End If

If dLookBack >= TargetValue Then
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 i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
CountAfterRunningSum = "Failed"

For Each cel In rgToSum.Cells
Set rgBack = Nothing
dLookBack = 0

Select Case IncludePivotCell
Case 0
Case 1
Set rgBack = cel
Case 2
Set rgBack = cel
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
End If

If dLookBack >= TargetValue Then