Forum Discussion

HyperbaricFrontDesk's avatar
HyperbaricFrontDesk
Copper Contributor
Apr 30, 2025
Solved

I want to track individual times, but there can be multiply times in one cell

I've tried three different formulas but none of them are giving me the data I want. It seems with '=SUM' formula, it's also counting 16:00 when I only want 6:00, I don't even know what the '=COUNTA' ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 01, 2025

    Do you want to count how many times the time 6:00 occurs?

    =SUM(--ISNUMBER(SEARCH(", 6:00, ", ", "&C10:C15&", ")))

    or using your first formula

    =LET(d, ", "&C10:C15&", ", t, ", 6:00, ", SUM(LEN(d)-LEN(SUBSTITUTE(d, t, "")))/LEN(t))

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 02, 2025

Assuming you have texts only and list of times is not extremely long, as variant that could be

=LET(
   data,        $B$3:$B$8,
   times,       TRIM( TEXTSPLIT( ARRAYTOTEXT(data), "," ) ),
   UniqueTimes, TOCOL( UNIQUE(times,1) ),
   SortedTimes, SORTBY(UniqueTimes, --UniqueTimes ),
   cross,       --(SortedTimes=times),
   count,       MMULT(cross,SEQUENCE( COLUMNS(times),,,0)  ),
   VSTACK( {"Time","Count"}, HSTACK( SortedTimes, count) )
 )