count number of cells that contain text string

[pigsfoot]

Hi, i have a colum that contains date and time as dd/mm/yyyy hh:mm.

I need a forumula that will count the number of entries in one column i.e. F2:F500 that contains a specific text string such as 00:00 or 00:15 or 00:30 and so on as i need to report how many enteries there are for each 15 min window throughout a day.

I seem to be strugalling as the number i'm search for contains ":" ( i believe its a text string ) rather than just a pure number.

Thanks

 

[bartmaster]

Hi,
See if this works for you.

Add a new "helper" column to extract the time value (col B), list all of the unique hours you would want to count (col D) and add formula to col E.

Book2
	A	B	C	D	E
1	date/time list	TimeValue		Hour	Count
2	03/12/2021 00:00	00:00		00:00	1
3	04/12/2021 00:15	00:15		00:15	3
4	05/12/2021 00:30	00:30		00:30	1
5	06/12/2021 00:45	00:45		00:45	1
6	07/12/2021 01:00	01:00		01:00	2
7	08/12/2021 00:15	00:15		01:15	1
8	09/12/2021 01:30	01:30		01:30	1
9	10/12/2021 01:45	01:45		01:45	2
10	11/12/2021 01:00	01:00		02:00	0
11	12/12/2021 00:15	00:15		02:15	0
12	13/12/2021 02:30	02:30		02:30	1
13	14/12/2021 01:45	01:45		02:45	0
14	15/12/2021 03:00	03:00		03:00	1
15	01/12/2021 01:15	01:15		03:15	0
Sheet4

Cell Formulas
Range	Formula
B2:B15	B2	=VALUE(TEXT(A2,"hh:mm"))
E2:E15	E2	=COUNTIF($B$2:$B$15,D2)