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#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
finnstoneAsked:
Who is Participating?
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.

byundtMechanical EngineerCommented:
If you are only counting the next twelve columns of data, I don't see why you would check the 13 columns O-AA.

Neglecting the above issue, I wrote a UDF along the same lines as for your previous question. You use it with a worksheet formula like:
=ColumnCount(H1:CA1,12,19000)           'Count excludes pivot cell
=ColumnCount(H1:CA1,12,19000,TRUE)         'Count includes pivot cell

Open in new window

Put the UDF in a regular module.
Function ColumnCount(rgToCheck As Range, LookAhead As Long, TargetValue As Double, Optional bIncludePivotCell As Boolean = False) As Variant
'When one of the values in rgToCheck exceeds TargetValue, then returns count of LookAhead number of cells that are >0.
'If bIncludePivotCell is False, it is not included in looking forward count
'If bIncludePivotCell is True, it is included in looking forward count
Dim cel As Range, Finish As Range, rgAhead As Range, Start As Range
Dim i As Long, j As Long
Set Start = rgToCheck.Cells(1)
Set Finish = rgToCheck.Cells(rgToCheck.Cells.Count)
ColumnCount = "Failed"

For Each cel In rgToCheck.Cells
    Set rgAhead = Nothing
    
    If bIncludePivotCell = True Then
        Set rgAhead = cel
    Else
        If Not cel Is Finish Then Set rgAhead = cel.Offset(0, 1)
    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))
    
        If cel.Value >= TargetValue Then
            ColumnCount = Application.CountIf(rgAhead, ">0")
            Exit Function
        End If
    End If
Next
End Function

Open in new window

RunningSumTestingQ29115305.xlsm
0

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
finnstoneAuthor Commented:
awesome
0
finnstoneAuthor Commented:
thx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

finnstoneAuthor Commented:
can you update this one too? it is skipping cell immediately following
0
byundtMechanical EngineerCommented:
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
0
finnstoneAuthor Commented:
perfect. just need the sum after single value threshold now :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.