Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Posts
    9
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Formula for Counting Backwards

    Does anyone know if there is a formula for counting backwards based on the current date and specific criteria?

    Basically, I have dates in column A, with data in both columns B and C, and Yes or No in column D.

    I would like the formula to look in column A for the current date, and then compare the Yes or No entry in column D on the same row as the current date. If the entry in column D is Yes, count backward (or up) the consecutive number of Yes entries.

    I have attached screenshot of an example worksheet and the desired results. Using the example, the answer Iím looking for in example 1 is 5 and example 2 is 0.

    Any ideas will be much appreciated.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Baseball,

    Here is a simple User Defined Function (UDF) that will easily do what you are looking for. This sample has a range of dates in col A, Yes/No values in column D, and assumes today is 2/21/2015 but it will work with any date.

    Baseball1.png

    In the cell where you want the result, enter the formula =RevCount(A1:A20) where A1:A20 is the range of the dates. In a standard module, enter the following code:

    Code:
    Public Function RevCount(rng As Range)
    Dim cell As Range
    Dim count As Integer
    count = 0
    LastRow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
    For Each cell In rng
        If cell = Date Then
            For I = cell.Row To rng.Row Step -1
                If Cells(I, 4) = "Yes" Then
                    count = count + 1
                Else:
                    GoTo Done
                End If
            Next I
        End If
    Next cell
    Done:
    RevCount = count
    End Function
    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    A non VBA approach:

    Using a helper column in column E to provide a running total of consecutive "Yes"

    In cell E1, place the formula =IF(D1="Yes",1,0) Different because it is the first row.
    In cell E2, place the formula =IF(D2="Yes",E1+1,0) then copy down

    In the cell you would like the result to appear, enter the formula
    =IF(INDEX(A1:E20,MATCH(TODAY(),A1:A20,0),4)="Yes", INDEX(A1:E20,MATCH(TODAY(),A1:A20,0),5),0)

    Baseball2.png

    This formula will look for the date in col A that matches today's date then looks across to column E for the number of consecutive "Yes". If the today's date row is a Yes, it will return that number in col E else it will return a zero. You can hide the entire helper column E, use a white font color to mask it, use a different column way off to the right, or put the helper column on a different sheet. Your preference.

    JTJ,
    Maud
    Last edited by Maudibe; 2015-02-21 at 16:59.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Nice work Maud!

    I took a slightly different tact assuming that there would be more than one list per sheet (probably wrong but that's how I read the example).

    Here's my shot at a UDF.

    Code:
    Option Explicit
    
    Function lCountConsecutiveUp(rngTarget As Range) As Long
    
       Dim lLastRow    As Long
       Dim lFirstRow   As Long
       Dim lCurRow     As Long
       Dim lCntr       As Long
       
       lCurRow = rngTarget.Row()
       
       Do Until ((Cells(lCurRow, 1).Value = Date) Or (Cells _
                 (lCurRow, 1).Value = ""))
                 
         lCurRow = lCurRow + 1
      
       Loop
       
       lLastRow = lCurRow
       
       lFirstRow = rngTarget.Row()
       lCntr = 0
       
       For lCurRow = lLastRow To lFirstRow Step -1
          If UCase(Cells(lCurRow, 4)) = "NO" Then
            Exit For
          Else
            lCntr = lCntr + 1
          End If
       Next lCurRow
                   
       lCountConsecutiveUp = lCntr
                   
    End Function  'lCountConsecutiveUp()
    Choices so many choices!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    baseball715 (2015-02-22)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Yes RG, many ways to skin a cat. I like your forethought of using Ucase which is a worthy precaution I always seem to forget. Nicely done!

    Maud

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    baseball715 (2015-02-22)

  8. #6
    New Lounger
    Join Date
    Feb 2009
    Posts
    9
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Maudibe and RetiredGeek,

    Thanks for your help. My knowledge is very limited when it comes to VBA. So, I took the non-VBA approach. That formula was exactly what I needed.

    Maudibe, I did play with the worksheet you attached and I like the approach. It was much cleaner. But, I have a couple questions.

    (1) If I made a change in column D (Yes or No) to test the code, I had to use the Shift F3 function to have the cell recalculate. I checked to ensure I had auto calculation turned-on and it was. So I'm not sure why the code does not auto calculate.

    (2) I decided to insert a couple of columns before column A and the code stopped working. Again, just to get a feel for the impact it would have on the UDF. I assumed the line of code: If Cells(I,4)=”Yes” referenced the fourth column (column D) so I changed it to 6 to represent column F, but that did not work. I checked to ensure that the formula in the cell where I wanted the results did change the date range to reflect =RevCode(C1:C20).

    Again, thanks to all for your help and quick response.

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Baseball,

    I'm not sure why the code does not auto calculate
    (1). I added a line of that will make the UDF recalculate if you change a value. Application.Volatile Good pickup!

    I decided to insert a couple of columns before column A and the code stopped working.
    (2). Again, very perceptive to change the 4 to a 6 in the line If Cells(I, 4) = "Yes" Then. You were also correct to check to make sure the range in the calling formula had adjusted. Together with the added line above, all is correct.

    I removed a not needed line and took the opportunity to use the wisdom from Retired Geek and make it case insensitive so the a Yes is the same as a YES or yes entry. Note in the spreadsheet that as the date changed to 2/22/2015 the returned value increased to 5

    HTH,
    Maud

    Code:
    Public Function RevCount(rng As Range)
    Application.Volatile
    Dim cell As Range
    Dim count As Integer
    count = 0
    For Each cell In rng
        If cell = Date Then
            For I = cell.Row To rng.Row Step -1
                If UCase(Cells(I, 6)) = "YES" Then
                    count = count + 1
                Else:
                    GoTo Done
                End If
            Next I
        End If
    Next cell
    Done:
    RevCount = count
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2015-02-22 at 09:36.

  10. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Straight formula method:

    =MATCH(TODAY(),$C$1:$C$20,0)-LOOKUP(2,1/($C$1:$C$20<TODAY())/($F$1:$F$20="no"),ROW($C$1:$C$20))
    Regards,
    Rory

    Microsoft MVP - Excel

  11. The Following User Says Thank You to rory For This Useful Post:

    zeddy (2015-02-23)

  12. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi rory

    Basically, I have dates in column A, with data in both columns B and C, and Yes or No in column D.
    Assuming the dates are in ascending order and the data is in different columns to what the poster said, there is a good chance your formula will work. But when I adjusted your formula for the relevant columns and sample ranges, and then tried it with a random set of dates in column A I didn't get the answer I expected.
    But both Maud and RG gave the correct count.
    What am I missing?

    zeddy

  13. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Nothing. The examples shown had dates in ascending order and the logic of the question made me figure it was probably a safe bet that it would be sorted that way. I should have mentioned the assumption though.

    (the formula was tailored to Maud's workbook since the OP's pdf was no use )
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi rory

    well, once I put my dates in order, your formula worked of course.
    Which is why I thanked you in advance.
    Though, for the life of me, I cannot understand the purpose of the original request.
    Maybe it has something to do with this game called 'baseball', which I believe is popular over there.

    zeddy

  15. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That's a corrupted version of rounders, is it not?
    Regards,
    Rory

    Microsoft MVP - Excel

  16. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Now, you wouldn't be insulting our national past time I hope?

  17. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    Absolutely not. That wouldn't be cricket.

    zeddy

  18. The Following User Says Thank You to zeddy For This Useful Post:

    rory (2015-02-24)

  19. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Pay no attention they are still upset about 1776 & 1812! ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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