Formula to determine how many times something occurred in a row

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?




  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

  • @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!



  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Where would you want this data to be populated?

  • edited 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


    Thank you for your time Paul!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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)

Sign In or Register to comment.