Issue with Countif not counting values due to formatting

  • Hi, I wasn’t sure if the formula Forum was the right one for this query but hope someone can help.

    I am trying to put together a spreadsheet that tracks in 30 minute increments how many people are watching football at any given time

    I have a attached a sample worksheet which hopefully shows what I have so far. In column K I have the start time which is populated if the word football appears in Column D then in Column L I have an end time based on the start time plus the duration they watched the sport for which is in Column

    Column N has 30 minute time slots, and in column O there is the count of how many concurrent people are watching the sport.

    From what I have seen so far (counting manually and comparing to the result of the formula in column O) this works. However I have an issue with the 07:00 time slot as it always displays 0 for the count. If I manually type ’07:00’ into column K it increases the count so I have it narrowed down to a formatting issue but I am un sure on how to rectify it.

    Once I have the data I plan on having it in graph form to trend the concurrent viewers.

    There may also be a much simpler way of doing this, and one which displays the information more accurately as I appreciate someone may go over the end time by 5 seconds which skews the count of the time slot if there is a way to report it more accurately than I would be happy to learn.

  • I'll just point out that your 07:00 in column H are not the precise 7 o'clock since all have a few seconds. That's why you won't find a response in column O.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!