Results 1 to 15 of 16
Thread: Formula for Counting Backwards

20150221, 15:26 #1
 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.

20150221, 16:19 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 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
Maud

20150221, 16:50 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 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,
MaudLast edited by Maudibe; 20150221 at 16:59.

20150221, 17:04 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 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()
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
baseball715 (20150222)

20150221, 17:16 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 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

The Following User Says Thank You to Maudibe For This Useful Post:
baseball715 (20150222)

20150222, 00:13 #6
 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 nonVBA 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 turnedon 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.

20150222, 01:52 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 Posts
Baseball,
I'm not sure why the code does not auto calculate
I decided to insert a couple of columns before column A and the code stopped working.
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
Last edited by Maudibe; 20150222 at 09:36.

20150223, 03:30 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 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

The Following User Says Thank You to rory For This Useful Post:
zeddy (20150223)

20150223, 04:21 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 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.
But both Maud and RG gave the correct count.
What am I missing?
zeddy

20150223, 04:28 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 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

20150223, 14:32 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 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

20150224, 04:05 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
That's a corrupted version of rounders, is it not?
Regards,
Rory
Microsoft MVP  Excel

20150224, 06:10 #13
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 Posts
Now, you wouldn't be insulting our national past time I hope?

20150224, 07:05 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 Posts
Hi Maud
Absolutely not. That wouldn't be cricket.
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
rory (20150224)

20150224, 18:07 #15
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 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