asked on

Count columns after hitting a threshold

continuation of this one-
https://www.experts-exchange.com/questions/29115268/LOOK-FOR-VALUE-THEN-TAKE-NEXT-12-COLUMNS-AND-SUM-THEM.html?anchorAnswerId=42663850#a42663850

I want to know how many cells have values greater than 0, after a cell hits 19,000
threshold is 19,000 , looking for the first time an array crosses that mark
note- an array is between columns H-CA
then i need to COUNTthe NEXt twelve columns of data.

so for example, if COL M has 17,500 AND col N has 19,400, and this was first time we crossed 19,000 (ie it didnt happen in colmns A-L, then i need to countcolumns O-Z, if only columns O-Y have values, then sum is 11, if columns O-AA have values than 13...need to check all the way to column CA

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

finnstone

ASKER

awesome

finnstone

ASKER

thx

finnstone

ASKER

can you update this one too? it is skipping cell immediately following

byundt

Call the revised function like this:

=CountAfterThreshold(H1:CA1,12,19000,0)

Open in new window

The revised code is shown below:

Function CountAfterThreshold(rgToCheck As Range, LookAhead As Long, TargetValue As Double, Optional IncludePivotCell As Long = 0) As Variant
'Examines values in rgToCheck. When one of those equals or exceeds TargetValue, then returns number of LookAhead columns with non-zero values.
'If LookAhead is 0, then all remaining columns in rgToCheck are included in the count
'If IncludePivotCell is 0, it is not included in looking forward count.
'If IncludePivotCell is 1, it is included in looking forward count
Dim cel As Range, Finish As Range, rgAhead As Range
Dim dLookAhead As Double
Dim FinishAddr As String
Dim i As Long, j As Long
Set Finish = rgToCheck.Cells(rgToCheck.Cells.Count)
FinishAddr = Finish.Address
CountAfterThreshold = "Failed"

For Each cel In rgToCheck.Cells
    If cel.Value >= TargetValue Then
        Set rgAhead = Nothing
        dLookAhead = 0
        
        Select Case IncludePivotCell
        Case 0
            If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
        Case 1
            Set rgAhead = cel
        End Select
            
        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
        
        CountAfterThreshold = dLookAhead
        Exit Function
    
    End If
Next
End Function

Open in new window

RunningSumTestingQ29115305-SUMMING-.xlsm

finnstone

ASKER

perfect. just need the sum after single value threshold now :)