Formula for Time - Unique user Count.

[Sindhura]

Hi,

I have a requirement where I would need help with the formula (in count field). Below is my requirement. Please help.
As per the given table, i need formula for "Count" field for every past 10minutes based on UNIQUE Names.

	Time	Count	Comments
	11:51 AM​	1​
	11:52 AM​	1​
	11:53 AM​	1​
	11:54 AM​	1​
	11:55 AM​	1​
	11:56 AM​	1​
	11:57 AM​	1​
	11:58 AM​	1​
	11:59 AM​	1​
Tom	12:00 PM​	1​
	12:01 PM​	0​
	12:11 PM​	1​
	12:12 PM​	1​
	12:13 PM​	1​
	12:14 PM​	1​
	12:15 PM​	1​
	12:16 PM​	2​	Here, the count was 2 because Noddy has entered at 12:25 where 12:16 was his past 10mins.
	12:17 PM​	2​
	12:18 PM​	3​	Here, the count was 3 because Mario has entered at 12:29 where 12:18 was his past 10mins.
	12:19 PM​	3​
Jerry	12:20 PM​	3​
	12:21 PM​	2​
	12:22 PM​	2​
	12:23 PM​	2​
	12:24 PM​	2​
Noddy	12:25 PM​	2​
	12:26 PM​	1​
	12:27 PM​	1​
	12:28 PM​	1​
Mario	12:29 PM​	1​
	12:30 PM​	0​

 

[JamesCanale]

Assuming it's 10 rows of data, and not 10 minutes - that is, there is one row per minute, even though 12:02 to 12:10 are missing here. And I can't figure how you have Mario starting at 12:18 instead of 12:20 - it doesn't seem consistent with Jerry and Noddy.

Also, is unique, just a unique count in the ten minutes in the future, or overall unique - meaning if they have appeared up on the list somewhere - do they never count again?

MrExcelPlayground12.xlsx
	A	B	C	D	E
2	Time	Count			Comments
3		11:51 AM	1	1
4		11:52 AM	1	1
5		11:53 AM	1	1
6		11:54 AM	1	1
7		11:55 AM	1	1
8		11:56 AM	1	1
9		11:57 AM	1	1
10		11:58 AM	1	1
11		11:59 AM	1	1
12	Tom	12:00 PM	1	1
13		12:01 PM	0	0
14		12:11 PM	1	1
15		12:12 PM	1	1
16		12:13 PM	1	1
17		12:14 PM	1	1
18		12:15 PM	1	1
19		12:16 PM	2	2	Here, the count was 2 because Noddy has entered at 12:25 where 12:16 was his past 10mins.
20		12:17 PM	2	2
21		12:18 PM	2	3	Here, the count was 3 because Mario has entered at 12:29 where 12:18 was his past 10mins.
22		12:19 PM	2	3
23	Jerry	12:20 PM	3	3
24		12:21 PM	2	2
25		12:22 PM	2	2
26		12:23 PM	2	2
27		12:24 PM	2	2
28	Noddy	12:25 PM	2	2
29		12:26 PM	1	1
30		12:27 PM	1	1
31		12:28 PM	1	1
32	Mario	12:29 PM	1	1
33		12:30 PM	0	0
Sheet23

Cell Formulas
Range	Formula
C3:C33	C3	=IFERROR(ROWS(UNIQUE(FILTER(A3:A12,A3:A12<>""))),0)

 

[Sindhura]

Hi James,

Seems my requirement was not clear earlier. To be more specific, we need unique user count by considering past 10mins.
Provided the new example to be more clear and understandable. We need formula for "Count" field . Please help!

Thank you

Note : If the same user is being logged in multiple times within the 10mins then the user should be counted only once considering the last timestamp.

Name	Time	Count	Comments
	12:20:00​	0
	12:28:00​	0
	12:38:00​	1	Here we got 1 because A entered at 12:44 so considering past 10mins i.e., from 12:35 to 12:44 the count should be 1
A	12:44:35​	1
	12:45:00​	0
	13:15:00​	0
	13:17:00​	1	Here we got 1 because B entered at 13:26 so considering past 10mins i.e., from 13:17 to 13:26 the count should be 1
	13:19:00​	1
B	13:26:34​	2	Here we got 2 because C entered at 13:29 so considering past 10mins i.e., from 13:20 to 13:29 the count should be 1 and since B is already there (1+1 =2)
	13:27:00​	1
C	13:29:56​	1
	13:30:00​	0
	13:38:51​	1	Here we got 1 because D entered at 13:40 so considering past 10mins i.e., from 13:31 to 13:40 the count should be 1
D	13:40:51​	1
E	13:53:00​	3	Here we got 3 because F & G entered at 14:00 & 14:01 so considering past 10mins i.e., from 13:51/13:52 to 14:00/14:01 the user should be counted ( E+F+G = 3)
F	14:00:14​	2	Here we got 2 because G entered at 14:01 so considering past 10mins i.e., from 13:51 to 14:00 the user G has to be considered (F+G=2)
G	14:01:00​	1

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C	D	E
38	Name	Time	Count		Comments
39		12:30:00	0	0
40		12:28:00	0	0
41		12:38:00	1	1	Here we got 1 because A entered at 12:44 so considering past 10mins i.e., from 12:35 to 12:44 the count should be 1
42	A	12:44:35	1	1
43		12:45:00	0	0
44		13:15:00	0	0
45		13:17:00	1	1	Here we got 1 because B entered at 13:26 so considering past 10mins i.e., from 13:17 to 13:26 the count should be 1
46		13:19:00	1	1
47	B	13:26:34	2	2	Here we got 2 because C entered at 13:29 so considering past 10mins i.e., from 13:20 to 13:29 the count should be 1 and since B is already there (1+1 =2)
48		13:27:00	1	1
49	C	13:29:56	1	1
50		13:30:00	0	0
51		13:38:51	1	1	Here we got 1 because D entered at 13:40 so considering past 10mins i.e., from 13:31 to 13:40 the count should be 1
52	D	13:40:51	1	1
53	E	13:53:00	3	3	Here we got 3 because F & G entered at 14:00 & 14:01 so considering past 10mins i.e., from 13:51/13:52 to 14:00/14:01 the user should be counted ( E+F+G = 3)
54	F	14:00:14	2	2	Here we got 2 because G entered at 14:01 so considering past 10mins i.e., from 13:51 to 14:00 the user G has to be considered (F+G=2)
55	G	14:01:00	1	1
Sheet23

Cell Formulas
Range	Formula
C39:C55	C39	=IFERROR(ROWS(FILTER($A$39:$A$55,($A$39:$A$55<>"")*($B$39:$B$55>=B39)*($B$39:$B$55<B39+10/60/24))),0)

 

[Sindhura]

Hi James,

Thanks for your time on this.
But the formula didn't worked out for me when I have checked : ( Sorry..
Could you please let me know if there is any alternative formula for this.

Thanks in advance!

 

[JamesCanale]

Since it appears to work for me, and I don't know how or why it doesn't work for you, I can't imagine what kind of alternative would be needed. If you could be more specific in what didn't work, that could be helpful.

 

[Sindhura]

Sorry for the confusion..

Formula worked

Thanks a lot!!