Count of unique list.

[Karthik23]

Hello,

Could anyone please help with the formula in 'Count' field. We need the unique user count considering the time for past 10mins.

Below is an example set for reference.

Thanks in advance!

Name	Time	Count	Comment
A	10/1/2021 9:06​	1
B	10/1/2021 9:26​	1
B	10/1/2021 9:26​	1
C	10/1/2021 11:01​	2	Here the count is 2 (C=1, D=1) because User D has to be counted from (10:53 to 11:02).
D	10/1/2021 11:02​	1	Here the count will be 1 because the user is same at 11:02 in below line.
D	10/1/2021 11:02​	1
E	10/1/2021 11:29​	1
F	10/1/2021 11:45​	1
G	10/1/2021 14:55​	1
H	9/2/2022 9:39​	2	Here the count is 2 (H=1, C=1) because User C has to be counted from (9:30 to 9:39).
C	9/2/2022 9:39​	1

 

[Karthik23]

Hi again, can someone please help me on this

 

[Peter_SSs]

Could anyone please help with the formula in 'Count' field. We need the unique user count considering the time for past 10mins.

Name	Time	Count	Comment
A	10/1/2021 9:06​	1
B	10/1/2021 9:26​	1
B	10/1/2021 9:26​	1
C	10/1/2021 11:01​	2	Here the count is 2 (C=1, D=1) because User D has to be counted from (10:53 to 11:02).
D	10/1/2021 11:02​	1	Here the count will be 1 because the user is same at 11:02 in below line.
D	10/1/2021 11:02​	1
E	10/1/2021 11:29​	1
F	10/1/2021 11:45​	1
G	10/1/2021 14:55​	1
H	9/2/2022 9:39​	2	Here the count is 2 (H=1, C=1) because User C has to be counted from (9:30 to 9:39).
C	9/2/2022 9:39​	1

I do not understand your expected results. For example, at 10/1/2021 11:01 as far as I understand it, there was only 1 user in the previous 10 minutes (user C).

It seems to me to be the same question that you asked here except that now the date and time are combined into one column, not two.
If that is the case, then I would be suggesting this variation of my previous formula.

Karthik23.xlsm
	A	B	C
1	Name	Time	Count
2	A	1/10/2021 9:06	1
3	B	1/10/2021 9:26	1
4	B	1/10/2021 9:26	1
5	C	1/10/2021 11:01	1
6	D	1/10/2021 11:02	2
7	D	1/10/2021 11:02	2
8	E	1/10/2021 11:29	1
9	F	1/10/2021 11:45	1
10	G	1/10/2021 14:55	1
11	H	2/09/2022 9:39	1
12	C	2/09/2022 9:39	2
Sheet2

Cell Formulas
Range	Formula
C2:C12	C2	=ROWS(UNIQUE(FILTER(A$2:A2,(B$2:B2>=B2-TIME(0,10,0))*(B$2:B2<=B2))))

 

[Karthik23]

Hello,

Could anyone please help with the formula in 'Count' field. We need the unique user count considering the time for past 10mins.

Below is an example set for reference.

Thanks in advance!

Name	Time	Count	Comment
A	10/1/2021 9:06​	1
B	10/1/2021 9:26​	1
B	10/1/2021 9:26​	1
C	10/1/2021 11:01​	2	Here the count is 2 (C=1, D=1) because User D has to be counted from (10:53 to 11:02).
D	10/1/2021 11:02​	1	Here the count will be 1 because the user is same at 11:02 in below line.
D	10/1/2021 11:02​	1
E	10/1/2021 11:29​	1
F	10/1/2021 11:45​	1
G	10/1/2021 14:55​	1
H	9/2/2022 9:39​	2	Here the count is 2 (H=1, C=1) because User C has to be counted from (9:30 to 9:39).
C	9/2/2022 9:39​	1

 

[Karthik23]

Hi Peter,

Thanks for the revert!

My requirement is little different from the old one. Hence, requested in the new thread.

The formula which you have provided is not fulfilling my requirement. The count for user 'C' should be 2 and not 1. The reason is, user 'D' has logged in at 11:02, considering his last 10mins i.e, 10:53 to 11:02, the user 'D' has to be counted '1' and in between user 'C' has already entered at 11:01 (which is in between of 10:53 to 11:02). Hence the count is 2 (C+D = 1+1 =2).

Hope this is clear!

Name	Time	Count	Comment
A	10/1/2021 9:06	1
B	10/1/2021 9:26	1
B	10/1/2021 9:26	1
C	10/1/2021 11:01	2	Here the count is 2 (C=1, D=1) because User D has to be counted from (10:53 to 11:02).
D	10/1/2021 11:02	1	Here the count will be 1 because the user is same at 11:02 in below line.
D	10/1/2021 11:02	1
E	10/1/2021 11:29	1
F	10/1/2021 11:45	1
G	10/1/2021 14:55	1
H	9/2/2022 9:39	2	Here the count is 2 (H=1, C=1) because User C has to be counted from (9:30 to 9:39).
C	9/2/2022 9:39	1

Appreciate your efforts!

 

[Peter_SSs]

Hope this is clear!

It is not to me.

C

10/1/2021 11:01

2

Here the count is 2 (C=1, D=1) because User D has to be counted from (10:53 to 11:02).

In this line, where did you get 10:53 to 11:02 from? It seems to have nothing to do with 11:01

H	9/2/2022 9:39	2	Here the count is 2 (H=1, C=1) because User C has to be counted from (9:30 to 9:39).
C	9/2/2022 9:39	1

In the first row here you say H=1, C=1 gives 2 because both H & C has to be counted from 9:30 to 9:39.
If both h & C has to be counted from 9:30 to 9:39 then why isn't the bottom row also 2 since again both H & C are in 9:30 to 9:39?

 

[Karthik23]

C

10/1/2021 11:01

2

Here the count is 2 (C=1, D=1) because User D has to be counted from (10:53 to 11:02).

In this line, where did you get 10:53 to 11:02 from? It seems to have nothing to do with 11:01

In this requirement, we should consider the next lines if any user enters within 10mins range. Hence, we are considering user 'D' who has entered at 11:02 (past 10mins for 11:02 is 10:53 to 11:02). In between user 'C' has also entered at 11:01. So the count should be '2'.

H	9/2/2022 9:39	2	Here the count is 2 (H=1, C=1) because User C has to be counted from (9:30 to 9:39).
C	9/2/2022 9:39	1

In the first row here you say H=1, C=1 gives 2 because both H & C has to be counted from 9:30 to 9:39.
If both h & C has to be counted from 9:30 to 9:39 then why isn't the bottom row also 2 since again both H & C are in 9:30 to 9:39?

As we don't have any other user logged in after 9:39, the count for user 'C' is 1. Whereas for 'H' we can see user 'C' has entered at same time so the count for 'H' is 2.

 

[Peter_SSs]

You keep referring to "past 10 minutes" but from the expected results, all I can assume is that you actually mean in the "next 10 minutes". If that is the case, then try this.

Karthik23.xlsm
	A	B	C
1	Name	Time	Count
2	A	1/10/2021 9:06	1
3	B	1/10/2021 9:26	1
4	B	1/10/2021 9:26	1
5	C	1/10/2021 11:01	2
6	D	1/10/2021 11:02	1
7	D	1/10/2021 11:02	1
8	E	1/10/2021 11:29	1
9	F	1/10/2021 11:45	1
10	G	1/10/2021 14:55	1
11	H	2/09/2022 9:39	2
12	C	2/09/2022 9:39	1
Sheet2

Cell Formulas
Range	Formula
C2:C12	C2	=ROWS(UNIQUE(FILTER(A2:A$12,(B2:B$12<=B2+TIME(0,10,0))*(B2:B$12>=B2))))