Count Same Value Appear In A Range Of Time

[ibmy]

Hi,

This thread similiar to "Number of Same Value Appear in a Range of Time" but different rule. Let me know if this violated the rule of this forum because I was thinking reply a new rule request in previous thread but the previous thread already answered and have a solution.

After go through a few sample of data, there were occasion that I will miss a cell that same value in a range of time but in different row. Previous thread, I focus on previous row.

Any result of outcome area welcome as long as objective is met.

research - time group.xlsb
	A	B	C	D	E	F	G	H	I
1		time	value	result
2		01:11:32	90.387
3		01:11:58	90.386
4		01:12:28	90.382
5		01:12:55	90.389
6		01:13:41	90.384
7		01:13:43	90.389	1	C5
8		01:13:51	90.387	1	C2
9		01:13:57	90.387	2	C8 & C2
10		01:31:14	90.388
11		01:31:32	90.384
12		01:31:40	90.385
13		01:31:44	90.383
14		01:32:09	90.384	1	C11. No count C6, not in 10 minutes before 1:32:09 - 1:22:09
15		01:32:15	90.386
16		01:32:18	90.385	1	C12
17		01:32:21	90.386	1	C15
18		01:32:31	90.385	2	C16 & C12
19		01:33:01	90.385	3	C18 ,C16 & C12
20		01:33:17	90.387		No count C9, C8, C2 not in 10 minutes before 1:33:17 - 1:23:17
21		02:01:00	90.386		No count C3, not exist in 10 minutes 2:01:00 - 1:51:00
22
Sheet7

If I use rule of previous thread, I will miss C5 for value 90.389 (C7) , C2 for value 90.387 (C8) , and other cell that not exist in previous row but in range of time.

 

[alz]

Hi try this!

=COUNTIFS($C$2:$C$21,C2,$B$2:$B$21,">="&B2-0.00694444444444444,$B$2:$B$21,"<="&B2)-1

 

[maabadi]

I use Column A As Helper column

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1	Helper Column	time	value	result
2	C2	1:11:32	90.387
3	C2	1:11:58	90.386
4	C2	1:12:28	90.382
5	C2	1:12:55	90.389
6	C2	1:13:41	90.384
7	C2	1:13:43	90.389	1	C5
8	C2	1:13:51	90.387	1	C2
9	C2	1:13:57	90.387	2	C8 & C2
10	C10	1:31:14	90.388
11	C10	1:31:32	90.384
12	C10	1:31:40	90.385
13	C10	1:31:44	90.383
14	C10	1:32:09	90.384	1	C11. No count C6, not in 10 minutes before 1:32:09 - 1:22:09
15	C10	1:32:15	90.386
16	C10	1:32:18	90.385	1	C12
17	C10	1:32:21	90.386	1	C15
18	C10	1:32:31	90.385	2	C16 & C12
19	C10	1:33:01	90.385	3	C18 ,C16 & C12
20	C10	1:33:17	90.387		No count C9, C8, C2 not in 10 minutes before 1:33:17 - 1:23:17
21	C21	2:01:00	90.386		No count C3, not exist in 10 minutes 2:01:00 - 1:51:00
Sheet1

Cell Formulas
Range	Formula
A2:A21	A2	="C"&IFNA(MATCH(LOOKUP(9.999999999999E+307,IF(B2-$B$2:$B$21>10/1440,$B$2:$B$21)),$B$1:$B$21,0)+1,2)
D2:D21	D2	=IF(COUNTIFS(INDIRECT(A2):C2,C2)-1=0,"",COUNTIFS(INDIRECT(A2):C2,C2)-1)
Press CTRL+SHIFT+ENTER to enter array formulas.