# Thread: Count IF code for Excel worksheet

1. ## Count IF code for Excel worksheet

Hello Everyone,

I have a spreadsheet with column called "Runorder" with only 0 and 1 values. I want a count fuction to start when the value is 1 and end the count when value is zero and store the count value in a cell. Also the count fuction should start the count again and keep iterating it till end of data in the column.

I am not very good with VBA coding and any help would be appreciated.

Thanks

2. I don't think it needs VBA, just a formula. Where do you want the values stored? Could you attach sample file setup with the values desired?

Steve

3. I'm sure Steve will come up with a better solution but here is some VBA to do the trick assuming you want the totals stored in the adjacent column at the end of the running 1's.
Code:
```Option Explicit

Sub RunOrderCounts()

Dim dColNo  As Double 'Run Order Column
Dim lCntr   As Long   'Row Counter
Dim lCount  As Long   '1's Running Count

lCntr = 2
lCount = 0
'*** Find RunOrder Column ***
dColNo = WorksheetFunction.Match("RunOrder", Range("1:1"), 0)
'*** Clear Previous totals, assumes totals in col to right of RunOrder
Cells(1, dColNo).End(xlDown).Select
Range(ActiveCell.Offset(0, 1), Cells(2, dColNo + 1)).ClearContents

Do

If Cells(lCntr, dColNo) = 1 Then
lCount = lCount + 1
End If

lCntr = lCntr + 1

If Cells(lCntr, dColNo) = 0 And _
lCount > 0 Then
Cells(lCntr - 1, dColNo + 1) = lCount
lCount = 0   'Reset Counter
End If

Loop While Cells(lCntr, dColNo) <> ""

End Sub```
RunCounts.JPG

4. With that type of setup, you can get the results with the formula in B2:
=IF(AND(A2=1,A3=0),SUM(A\$1:A2)-SUM(B\$1:B1),"")

Then copy it down the column...

Steve

5. If only I could predict the stock market as well as I can predict Steve.
Nice job! If I could just learn to think like Steve.
RunCountsSteve.JPG

6. If I could just learn to think like Steve
I know many people (my wife and kids ex=specially) who would consider that to be a bad thing and something they would threaten as a curse

Steve

7. Steve & Retired Greek.....bolded & Bookmark for Excel formulas and VBA!!!!! Thanks for the insight

8. Thank you everyone for the quick help...Steve ur solution was very simple and ez as I dont have to work with VBA anymore

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•