Count IF code for Excel worksheet

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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. :cheers:

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

31088-RunCounts

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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. :cheers:
31094-RunCountsSteve

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote