Forum Discussion
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' formula is doing, and the '=COUNTIF' can only count when there's one time in a cell.
p.s. The cells are specified as text so excel doesn't register it as an actual time.
Please help me, I've spent three days on this.
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))
2 Replies
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) ) )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))
