Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 Posts
    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
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 Posts
    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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    177
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Steve & Retired Greek.....bolded & Bookmark for Excel formulas and VBA!!!!! Thanks for the insight
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  8. #8
    New Lounger
    Join Date
    Jun 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •