Help to count cells in a time range that meet a condition

[Alex Oliveira]

Hi there,



Please, I need help with this.



I have a series of hourly temperature data (C) for a year (N=8760) and I need to:



A) Count the number of cells (Column B) between this time range in a year: 01:00 to 08:00 AND 23:00 and 00:00.

I know the result is 2920 and would like to get that without using filter with SUBTOTAL.



B) Count the number of cells at the same time range that are lower than 30, in column C.

Year.xls
	A	B	C	D	E	F	G	H	I
1	Date/Time	Hour	To (°C)			Occupancy (Time ranges)	1:00	to	8:00
2			DORM1				23:00	to	0:00
3	01/01/2002 01:00	01:00	28,3
4	01/01/2002 02:00	02:00	27,7			Criteria	<30
5	01/01/2002 03:00	03:00	28,0
6	01/01/2002 04:00	04:00	28,3		A)	Count (Year)			2920
7	01/01/2002 05:00	05:00	28,2		B)	Count If (Year) <30
8	01/01/2002 06:00	06:00	27,9
9	01/01/2002 07:00	07:00	27,8
10	01/01/2002 08:00	08:00	27,9
11	01/01/2002 09:00	09:00	28,6
12	01/01/2002 10:00	10:00	29,4
13	01/01/2002 11:00	11:00	30,1
14	01/01/2002 12:00	12:00	30,7
15	01/01/2002 13:00	13:00	31,2
16	01/01/2002 14:00	14:00	31,6
17	01/01/2002 15:00	15:00	31,9
18	01/01/2002 16:00	16:00	32,3
19	01/01/2002 17:00	17:00	32,5
20	01/01/2002 18:00	18:00	32,4
21	01/01/2002 19:00	19:00	31,9
22	01/01/2002 20:00	20:00	31,3
23	01/01/2002 21:00	21:00	30,7
24	01/01/2002 22:00	22:00	30,2
25	01/01/2002 23:00	23:00	29,9
26	02/01/2002	00:00	29,7
27	02/01/2002 01:00	01:00	29,6
28	02/01/2002 02:00	02:00	29,4
29	02/01/2002 03:00	03:00	29,3
30	02/01/2002 04:00	04:00	29,2
31	02/01/2002 05:00	05:00	29,0
32	02/01/2002 06:00	06:00	28,6
33	02/01/2002 07:00	07:00	28,2
34	02/01/2002 08:00	08:00	28,4
35	02/01/2002 09:00	09:00	29,1
36	02/01/2002 10:00	10:00	29,9
37	02/01/2002 11:00	11:00	30,6
38	02/01/2002 12:00	12:00	31,1
39	02/01/2002 13:00	13:00	31,6
40	02/01/2002 14:00	14:00	32,0
41	02/01/2002 15:00	15:00	32,5
42	02/01/2002 16:00	16:00	32,9
43	02/01/2002 17:00	17:00	33,2
44	02/01/2002 18:00	18:00	33,1
45	02/01/2002 19:00	19:00	32,5
46	02/01/2002 20:00	20:00	31,8
47	02/01/2002 21:00	21:00	31,1
48	02/01/2002 22:00	22:00	30,5
49	02/01/2002 23:00	23:00	30,3
50	03/01/2002	00:00	29,4
L

I have been working with that, obtaining a partial solution using COUNT.IFS for a day (just one time interval), but when I have a year and different time intervals in a day….



Best regards,

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about
Note that I2 needs to be 1 or 24:00:00

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Date/Time	Hour	To (°C)			Occupancy (Time ranges)	01:00:00	to	08:00:00
2			DORM1				23:00:00	to	1
3	01/01/2002 01:00	01:00:00	28.32386
4	01/01/2002 02:00	02:00:00	27.66525			Criteria	<30
5	01/01/2002 03:00	03:00:00	27.95425
6	01/01/2002 04:00	04:00:00	28.30235		A)	Count (Year)	9		2920
7	01/01/2002 05:00	05:00:00	28.16119		B)	Count If (Year) <30	9
8	01/01/2002 06:00	06:00:00	27.94404
9	01/01/2002 07:00	07:00:00	27.79674
10	01/01/2002 08:00	08:00:00	27.88345
11	01/01/2002 09:00	09:00:00	28.6077
12	01/01/2002 10:00	10:00:00	29.4043
13	01/01/2002 11:00	11:00:00	30.06572
14	01/01/2002 12:00	12:00:00	30.68637
15	01/01/2002 13:00	13:00:00	31.17154
16	01/01/2002 14:00	14:00:00	31.55785
17	01/01/2002 15:00	15:00:00	31.91505
18	01/01/2002 16:00	16:00:00	32.30765
19	01/01/2002 17:00	17:00:00	32.53089
20	01/01/2002 18:00	18:00:00	32.40727
21	01/01/2002 19:00	19:00:00	31.93529
22	01/01/2002 20:00	20:00:00	31.30671
23	01/01/2002 21:00	21:00:00	30.69806
24	01/01/2002 22:00	22:00:00	30.17499
25	01/01/2002 23:00	23:00:00	29.87052
26	02/01/2002 00:00	00:00:00	29.7454
27	02/01/2002 01:00	01:00:00	29.56661
28	02/01/2002 02:00	02:00:00	29.35742
29	02/01/2002 03:00	03:00:00	29.32743
30	02/01/2002 04:00	04:00:00	29.22023
31	02/01/2002 05:00	05:00:00	28.97587
32	02/01/2002 06:00	06:00:00	28.62246
33	02/01/2002 07:00	07:00:00	28.20633
34	02/01/2002 08:00	08:00:00	28.43674
35	02/01/2002 09:00	09:00:00	29.11732
36	02/01/2002 10:00	10:00:00	29.85921
37	02/01/2002 11:00	11:00:00	30.55506
38	02/01/2002 12:00	12:00:00	31.12852
39	02/01/2002 13:00	13:00:00	31.55282
40	02/01/2002 14:00	14:00:00	31.98317
41	02/01/2002 15:00	15:00:00	32.45958
42	02/01/2002 16:00	16:00:00	32.94049
43	02/01/2002 17:00	17:00:00	33.20225
44	02/01/2002 18:00	18:00:00	33.06796
45	02/01/2002 19:00	19:00:00	32.53275
46	02/01/2002 20:00	20:00:00	31.80678
47	02/01/2002 21:00	21:00:00	31.11744
48	02/01/2002 22:00	22:00:00	30.53304
49	02/01/2002 23:00	23:00:00	30.34689
50	03/01/2002 00:00	00:00:00	29.39266
Database

Cell Formulas
Range	Formula
G6	G6	=COUNT(FILTER(B3:B50,((B3:B50>=G1)*(B3:B50<=I1))+((B3:B50>=G2)*(B3:B50<=I2))))
G7	G7	=COUNT(FILTER(B3:B50,((C3:C50<30)*(B3:B50>=G1)*(B3:B50<=I1))+((C3:C50<30)*(B3:B50>=G2)*(B3:B50<=I2))))