CODEWITHSUNDEEP
excelvba
Rkayy
Rkayy

Reputation: 37

How to count cells until a value is greater than 0?

In Excel I am trying to count the number of days until an appointment is available.

in the below data set I need to be able to count until a value is reached that is not 0.

the result I need is

In plain English I need it to check if cell = 0 if it is then count it, and stop counting when cell is no longer = 0

If there is a VBA solution that would be best but ill accept anything that works. 

            Example1    Example2   Example3
May 13 2019    0           0          2
May 14 2019    0           0          0
May 15 2019    0           0          6
May 16 2019    6           0          0
May 17 2019    0           0          3
May 20 2019    3           7          0
May 21 2019    6           14         0
May 22 2019    6           0          1
May 23 2019    12          14         0
May 24 2019    7           0          0

I have tried multiple methods however the closest i got was with the below VBA which seems to give the right answer before crashing my excel so I suspect its counting something it shouldn't. 

    Dim iCntr As Integer
    iCntr = 2
    Do While (Cells(iCntr, 3).Value) = 0
    Range("C13").Value = Application.WorksheetFunction.Count("C:C")
    Loop
End Sub

Upvotes: 1

Views: 3023

Answers (6)

Skin
Skin

Reputation: 11197

Yet another option using an array formula that works anywhere (at least I think it does) ...

{=MIN(IF(B4:B13>0,ROW(B4:B13)-MIN(ROW(B4:B13)),""))}

enter image description here

Be sure to commit using Shift + Ctrl + Enter

Upvotes: 0

Samuel Hulla
Samuel Hulla

Reputation: 7089

Like this:

Public Function count_zeroes(ByVal columnID As Long) As Long
    Dim i As Long: i = 1
    Dim cell As Range: Set cell = ActiveSheet.Cells(i, columnID)

    If Not IsEmpty(cell) Then
       Do Until cell <> 0 'we'll keep counting until cell <> 0 
           i = i + 1
           Set cell = ActiveSheet.Cells(i, columnID)
       Loop
    End IF

    count_zeroes = i - 1

End Function

enter image description here

Upvotes: 0

MrRightSA
MrRightSA

Reputation: 90

Formula;

For example 1 but editing returns the example 2 and 3.

=MATCH(TRUE,INDEX($B$2:$B$11>0,0),0)-1

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Function DAYS_UNTIL_APPOINTMENT(ByVal OnThisRange As Range) As Byte
Dim rng As Range


For Each rng In OnThisRange
    If rng.Value <> 0 Then
        Exit For
    Else
        DAYS_UNTIL_APPOINTMENT = DAYS_UNTIL_APPOINTMENT + 1
    End If
Next rng
End Function

enter image description here

Please, note this only will work if you select 1 column of data. Also, I made it Byte type, so if the number of days is higher than 255, then it will raise an error. Just change it to Integer if you need it.

Upvotes: 1

Pspl
Pspl

Reputation: 1474

Perhaps the simplest way is the next formula:

=IFERROR(MATCH(0,B:B,1)-MATCH(0,B:B,0)+1;0)

Assuming we're dealing with data on column B.

Upvotes: 2

tnavidi
tnavidi

Reputation: 1418

you could use a match formula =IF(B2<>0,0,MATCH(0,B2:B20,1)) with ascending order (edit:added if as this doesn't work if first day is available)

Upvotes: 0

Related Questions