Formula to determine how many times something occurred in a row

Rainier Hollands · 04/16/20

Hi everyone,

For my business I have multiple trucks, and every morning we record the statuses of the trucks before they start driving. I have multiple columns, each one being the different trucks. In the rows for those trucks I record the statuses for the different trucks. So I have "Available","Breakdown" etc.

I'm trying to write a formula in which I see how many days in a row that Truck has had that status for from the most recent date. Would someone please be able to assist with this?

Regards

Rainier

Paul Newcome · 04/16/20

Are you able to provide a screenshot of a mocked up sheet that shows what you are trying to accomplish? I am sure this is possible, but I want to make sure I am visualizing it properly.

Rainier Hollands · 04/20/20

Shift Start (VR) - Grid.xlsx

@Paul Newcome

Hi Paul, here is a copy of the sheet that I'm using.

Essentially what I'm looking for is a formula that shows the current status and also counts how many the current status has occured in a row. For Truck 11 it would be "Available" and 3, Truck 13: it would be "Breakdown" and 1, Truck 14: it would be "Available" and 51, Truck 16: it would be "Accident" and 7.

I look forward to hearing from you!

Regards

Rainier

Paul Newcome · 04/20/20

Where would you want this data to be populated?

Rainier Hollands · 04/20/20

@Paul Nelson

I've used a report which has all my list of trucks, statuses and time

So I currently have a table like this (Example)

_______________Status__________No of day in current Status

Truck 11______Available _______(This is where i want the formula to be which returns just a number)

Truck 13______Breakdown

Truck 14______Accident

Etc

Thank you for your time Paul!

Paul Newcome · 04/20/20

The easiest way I can think of to accomplish this would be to add in a checkbox for each of the trucks on the source sheet then check the boxes for each status in the run that does not need to be counted using something along the lines of...

=IF(COUNTIFS([Truck 11]$1:[Truck 11]@row, @cell <> INDEX([Truck 11]:[Truck 11], 1)), 1)

Then on the metrics sheet, you would use this to pull the current status:

=INDEX({Other Sheet Truck 11 Column}, 1)

And then this to count how many days it has been this status:

=COUNTIFS({Other Sheet Truck 11 Checkbox Column}, @cell <> 1)

Formula to determine how many times something occurred in a row

Answers

Help Article Resources

Categories