CODEWITHSUNDEEP
excel
LuckyLou
LuckyLou

Reputation: 11

Count consecutive instances in column

I am tracking my days on duty and want excel to count the number of consecutive days on duty and post the totals next to the current date. I managed to insert a formula that does this but it counts in reverse order. In the attached image of the file note that on 6/13/2018 it shows 28 continuous days on duty. But that is counted from 7/10/2018 back. I want that 28 to show up on 7/10/2018. So that it counts from the previous days to the current day. Make sense????

What do I need to change in the formula to do this? Formula can be seen in the image.

Any suggestions would be appreciated

https://i.sstatic.net/zNwoK.jpg

Upvotes: 1

Views: 1603

Answers (2)

FocusWiz
FocusWiz

Reputation: 533

I guess the picture helps a bit, but I would have preferred to see the data, even though this is simple enough to reproduce.

You probably want the formula: =IF(AND(D2="On Duty",D2=D1),E1+1,1)

Starting in Cell E2 and copied down from there.

Upvotes: 1

GalAbra
GalAbra

Reputation: 5138

You need to build your formula the other way around:
If row r-1 has the same status as row r, then Counter_r should be Counter_(r-1) + 1. Otherwise, restart the counter - and Counter_r = 1

So, for example, in cell E3 the formula should be:

=IF(D3=D2, E2+1, 1)

Upvotes: 1

Related Questions