Formula to determine how many times something occurred in a row
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
Answers
-
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.
-
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
-
Where would you want this data to be populated?
-
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!
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!