# 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
###### 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.

Mechanical 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
``````
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

If bIncludePivotCell = True Then
Else
If Not cel Is Finish Then Set rgAhead = cel.Offset(0, 1)
End If

If Not rgAhead Is Nothing Then

If cel.Value >= TargetValue Then
Exit Function
End If
End If
Next
End Function
``````
RunningSumTestingQ29115305.xlsm
0

Experts Exchange Solution brought to you by

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

Author Commented:
awesome
0
Author Commented:
thx
0
Author Commented:
can you update this one too? it is skipping cell immediately following
0
Mechanical EngineerCommented:
Call the revised function like this:
``````=CountAfterThreshold(H1:CA1,12,19000,0)
``````
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 i As Long, j As Long
Set Finish = rgToCheck.Cells(rgToCheck.Cells.Count)
CountAfterThreshold = "Failed"

For Each cel In rgToCheck.Cells
If cel.Value >= TargetValue Then

Select Case IncludePivotCell
Case 0
Case 1
End Select

If Not rgAhead Is Nothing Then
End If

Exit Function

End If
Next
End Function
``````
RunningSumTestingQ29115305-SUMMING-.xlsm
0
Author 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.