Count Unique Alphanumeric numbers in a column with a date range criteria (not using array)

[bethlc]

I'm wanting to count unique alphanumeric value from column 'user ID', but I'm wanting to include a criteria of date range where column 'Date' is between 12/12/2019 - 18/12/2019. I've tried numerous array formula's but they're all too slow so i'm wanting to know if it's possible to do it without an array formula?

If possible, I also want to do it with more than one criteria, e.g unique User ID's with a date range and where 'Did you make a change?' is 'Y'.

Member	Date	User ID	Did you make a change?
Louise	12/12/2019	12323324435436dfdfgerg	Y
Louise	12/11/2019	dgf5542342344323432432	Y
Louise	12/11/2019	dgf5542342344323432432	Y
Louise	12/11/2019	dgf5542342344323432432	Y
Louise	12/11/2019	dgf5542342344323432432	Y
Louise	12/11/2019	34534t34g34243j76i67yyt	Y
Louise	12/11/2019	43r243tg5h6uu5643543fg	Y
Louise	12/11/2019	534h65j7776g34r4fgg5y5y	N
Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N
Bob	16/12/2019	5yg35yhjjj8i8iu5h56456h6	N
Bob	16/12/2019	j875t245y45u7i75555r5t45	Y
Bob	16/12/2019	j875t245y45u7i75555r5t45	Y
Harry	16/12/2019	j875t245y45u7i75555r5t45	Y
Harry	16/12/2019	j875t245y45u7i75555r5t45	Y
Harry	16/12/2019	12323324435436dfdfgerg	N
Harry	16/12/2019	12323324435436dfdfgerg	N
Harry	16/12/2019	12323324435436dfdfgerg	N
Harry	17/12/2019	12323324435436dfdfgerg	N
Harry	17/12/2019	12323324435436dfdfgerg	N
Harry	17/12/2019	12323324435436dfdfgerg	N
Harry	17/12/2019	12323324435436dfdfgerg	N
Harry	17/12/2019	5yg35yhjjj8i8iu5h56456h6	N
Harry	17/12/2019	5yg35yhjjj8i8iu5h56456h6	N
Harry	17/12/2019	5v64563g5f45njj8k9k9k9k	N
Harry	17/12/2019	cv57867k867hhhh75h7586	N
Amy	16/12/2019	56n456456gd7j69k78k9k9m	N
Amy	16/12/2019	56n456456gd7j69k78k9k9m	N
Amy	24/12/2019	56n456456gd7j69k78k9k9m	N
Amy	24/12/2019	56n456456gd7j69k78k9k9m	N
Amy	24/12/2019	56n456456gd7j69k78k9k9m	N
Amy	16/12/2019	56n456456gd7j69k78k9k9m	N
Tegan	16/12/2019	d4346578989ll873h56g5645	Y
Tegan	16/12/2019	jiu6465b156g467hjkjkk8678	Y
Tegan	16/12/2019	546gv7bn775432v54346g6h	Y
Tegan	24/12/2019	324356ghhhj8989kd4324rt3	N
Megan	25/12/2019	324356ghhhj8989kd4324rt3	N
Megan	26/12/2019	324356ghhhj8989kd4324rt3	N
Megan	27/12/2019	nj7bn65643g34343ffffffffff	Y
Megan	28/12/2019	223d4fgf5g3g5gvgvbvb687	Y
Megan	29/12/2019	m6d1425r6g546g77j87j857	Y
Megan	30/12/2019	k4109385734h59834rggfgg	N
Megan	14/01/2019	m6d1425r6g546g77j87j857	Y

 

[jasonb75]

A helper column should speed things up a bit.

I've gone a bit overboard with the formula below, it would work with just the countifs part but the additional logic checks will help to process rows that don't match the criteria faster.

Book1
	A	B	C	D	E	F	G	H
1	Member	Date	User ID	Did you make a change?	Unique		Start date	16/12/2019
2	Louise	12/12/2019	12323324435436dfdfgerg	Y	0		End date	17/12/2019
3	Louise	12/11/2019	dgf5542342344323432432	Y	0		Change	Y
4	Louise	12/11/2019	dgf5542342344323432432	Y	0		Count of unique	4
5	Louise	12/11/2019	dgf5542342344323432432	Y	0
6	Louise	12/11/2019	dgf5542342344323432432	Y	0
7	Louise	12/11/2019	34534t34g34243j76i67yyt	Y	0
8	Louise	12/11/2019	43r243tg5h6uu5643543fg	Y	0
9	Louise	12/11/2019	534h65j7776g34r4fgg5y5y	N	0
10	Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
11	Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
12	Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
13	Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
14	Louise	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
15	Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
16	Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
17	Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
18	Bob	12/11/2019	5yg35yhjjj8i8iu5h56456h6	N	0
19	Bob	16/12/2019	5yg35yhjjj8i8iu5h56456h6	N	0
20	Bob	16/12/2019	j875t245y45u7i75555r5t45	Y	1
21	Bob	16/12/2019	j875t245y45u7i75555r5t45	Y	0
22	Harry	16/12/2019	j875t245y45u7i75555r5t45	Y	0
23	Harry	16/12/2019	j875t245y45u7i75555r5t45	Y	0
24	Harry	16/12/2019	12323324435436dfdfgerg	N	0
25	Harry	16/12/2019	12323324435436dfdfgerg	N	0
26	Harry	16/12/2019	12323324435436dfdfgerg	N	0
27	Harry	17/12/2019	12323324435436dfdfgerg	N	0
28	Harry	17/12/2019	12323324435436dfdfgerg	N	0
29	Harry	17/12/2019	12323324435436dfdfgerg	N	0
30	Harry	17/12/2019	12323324435436dfdfgerg	N	0
31	Harry	17/12/2019	5yg35yhjjj8i8iu5h56456h6	N	0
32	Harry	17/12/2019	5yg35yhjjj8i8iu5h56456h6	N	0
33	Harry	17/12/2019	5v64563g5f45njj8k9k9k9k	N	0
34	Harry	17/12/2019	cv57867k867hhhh75h7586	N	0
35	Amy	16/12/2019	56n456456gd7j69k78k9k9m	N	0
36	Amy	16/12/2019	56n456456gd7j69k78k9k9m	N	0
37	Amy	24/12/2019	56n456456gd7j69k78k9k9m	N	0
38	Amy	24/12/2019	56n456456gd7j69k78k9k9m	N	0
39	Amy	24/12/2019	56n456456gd7j69k78k9k9m	N	0
40	Amy	16/12/2019	56n456456gd7j69k78k9k9m	N	0
41	Tegan	16/12/2019	d4346578989ll873h56g5645	Y	1
42	Tegan	16/12/2019	jiu6465b156g467hjkjkk8678	Y	1
43	Tegan	16/12/2019	546gv7bn775432v54346g6h	Y	1
44	Tegan	24/12/2019	324356ghhhj8989kd4324rt3	N	0
45	Megan	25/12/2019	324356ghhhj8989kd4324rt3	N	0
46	Megan	26/12/2019	324356ghhhj8989kd4324rt3	N	0
47	Megan	27/12/2019	nj7bn65643g34343ffffffffff	Y	0
48	Megan	28/12/2019	223d4fgf5g3g5gvgvbvb687	Y	0
49	Megan	29/12/2019	m6d1425r6g546g77j87j857	Y	0
50	Megan	30/12/2019	k4109385734h59834rggfgg	N	0
51	Megan	14/01/2019	m6d1425r6g546g77j87j857	Y	0
Sheet1

Cell Formulas
Range	Formula
H4	H4	=SUM(E:E)
E2:E51	E2	=--IF(AND(B2>=$H$1,B2<=$H$2,D2=$H$3),COUNTIFS(B$2:B2,">="&$H$1,B$2:B2,"<="&$H$1,D$2:D2,$H$3,C$2:C2,C2)=1)

 

[bluesky63]

You can use power query editor which uses in-memory and can handle millions records quite rapidly

User ID	Check_Period	Count
12323324435436dfdfgerg	include	8​
5yg35yhjjj8i8iu5h56456h6	include	3​
j875t245y45u7i75555r5t45	include	4​
5v64563g5f45njj8k9k9k9k	include	1​
cv57867k867hhhh75h7586	include	1​
56n456456gd7j69k78k9k9m	include	3​
d4346578989ll873h56g5645	include	1​
jiu6465b156g467hjkjkk8678	include	1​
546gv7bn775432v54346g6h	include	1​

Power Query applied Steps

 

[jasonb75]

In addtion to the helper column method, this is the array method that I would use (if you haven't already tried it).

{=SUM(IFERROR(1/COUNTIFS($C$2:$C$51,$C$2:$C$51,$B$2:$B$51,">="&$H$1,$B$2:$B$51,"<="&$H$1,$D$2:$D$51,$H$3),0))}

If your data range is variable then I would suggest using dynamic named ranges instead of oversized fixed ranges or full columns, one common cause for slow array formulas is including a lot of empty rows in the data ranges. If you use entire columns in a formula with only 10k rows of data then that means that over 99% of the effort is wasted