Formula for Counting Backwards

Topic

New Reply

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.

Reply | Quote

Replies

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.

39517-Baseball1

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

Reply | Quote

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)

39518-Baseball2

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

Reply | Quote

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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))

Reply | Quote

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

Reply | Quote

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 ;))

Reply | Quote

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

Reply | Quote

That’s a corrupted version of rounders, is it not?

Reply | Quote

Now, you wouldn’t be insulting our national past time I hope?

Reply | Quote

Hi Maud

Absolutely not. That wouldn’t be cricket.

zeddy

Reply | Quote

Now, you wouldn’t be insulting our national past time I hope?

No, only your current pastime.

Couldn’t resist.

Reply | Quote

Maud,

Pay no attention they are still upset about 1776 & 1812! 35623-ROTFLOL
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote