Counting the number of unique dates across multiple tabs

[Beagle263]

I am trying to calculate the number of unique dates across multiple tabs, excluding any duplicate numbers.

I have tried =SUMPRODUCT(1/COUNTIF(Range,Range) which is works when applying this to only one of the tabs. However when I am trying to combine all the sheets I comes up as an Error.

 

[Fluff]

Which column contains the dates & are the sheets all consecutive?

 

[Beagle263]

There are roughly 3 sheets similar to the one below but just refers to different tests.

I need to calculate the number of days a technician has attended site from the tests done

Testing Location
Test No.	Date Tested	Plot	Grid Square
1	30/11/2023	3	L84
2	30/11/2023	3	L85
3	30/11/2023	3	M85
4	30/11/2023	3	M84
5	01/12/2023	3	M84
6	01/12/2023	3	M85
7	01/12/2023	3	N84
8	01/12/2023	3	N85
9	05/12/2023	2	W47
10	05/12/2023	2	X46
11	05/12/2023	2	W47
12	05/12/2023	2	X46
13	05/12/2023	2	J86
14	05/12/2023	3	L85
15	05/12/2023	3	L84
16	05/12/2023	2	X48
17	05/12/2023	2	X46
18	05/12/2023	3	N85
19	05/12/2023	3	M84
20	11/12/2023	3	L86
21	11/12/2023	3	L85
22	11/12/2023	3	L85
23	11/12/2023	3	L85
24	11/12/2023	3	L86
25	11/12/2023	3	L86
26	12/12/2023	3	M86
27	12/12/2023	3	M86
28	18/12/2023	3	L86
29	18/12/2023	3	K86
30	18/12/2023	3	K85
31	18/12/2023	3	G89
32	18/12/2023	3	H85
33	18/12/2023	3	H86
34	18/12/2023	3	K85
35	18/12/2023	3	K86
36	09/01/2024	2	X43
37	09/01/2024	2	X44
38	09/01/2024	2	X43
39	09/01/2024	2	X44
40	09/01/2024	2	X44
41	09/01/2023	2	X43
42	10/01/2024	2	X44
43	10/01/2024	2	X43
44	10/01/2024	2	X44
45	10/01/2024	2	X43
46	10/01/2024	2	X43
47	10/01/2024	2	X44
48	10/01/2024	2	X44
49	10/01/2024	2	X43
50	11/01/2024	2	X42
51	11/01/2024	2	X43
52	11/01/2024	2	X43
53	11/01/2024	2	X44
54	11/01/2024	2	X43
55	11/01/2024	2	X44
56	11/01/2024	2	Y42
57	11/01/2024	2	X43
58	11/01/2024	2	X43
59	11/01/2024	2	Y42
60	12/01/2024	2	Y42
61	12/01/2024	2	X43
62	12/01/2024	2	Y42
63	12/01/2024	2	X43
64	12/01/2024	2	X44
65	12/01/2024	2	X43
66	12/01/2024	2	Y45
67	12/01/2024	2	X44
68	15/01/2024	2	Y42
69	15/01/2024	2	Y43
70	15/01/2024	2	Y42
71	16/01/2024	2	Y43
72	16/01/2024	2	Y42
73	16/01/2024	2	X44
74	16/01/2024	2	X44
75	16/01/2024	2	Y42
76	16/01/2024	2	Y41
77	16/01/2024	2	Y43
78	16/01/2024	2	Y42
79	16/01/2024	2	Y41
80	16/01/2024	2	Y41
81	17/01/2024	2	Y42
82	17/01/2024	2	Y41
83	17/01/2024	2	Y43
84	17/01/2024	2	Y42
85	17/01/2024	2	Y43

 

[Fluff]

Thanks for that, unfortunately it doesn't answer my questions, but try something like

=LET(v,VSTACK(Sheet1!B2:B100,Sheet2!B2:B100,Sheet3!B2:B100),ROWS(UNIQUE(FILTER(v,v<>""))))

or if they are consecutive

=LET(v,VSTACK(Sheet1:Sheet3!B2:B100),ROWS(UNIQUE(FILTER(v,v<>""))))

 

[LegendaryAxe]

Could you try something like this? Put all your ranges in a VSTACK formula and then wrap in UNIQUE and COUNT function:

S7_BuiltinFunctions.xlsm
	Q
3	8
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNT(UNIQUE(VSTACK(B4:B53,G4:G53,L4:L53)))

 

[LegendaryAxe]

S7_BuiltinFunctions.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Testing Location					Testing Location					Testing Location
2																	Unique Count of Three Ranges
3	Test No.	Date Tested	Plot	Grid Square		Test No.	Date Tested	Plot	Grid Square		Test No.	Date Tested	Plot	Grid Square			8
4	1	30/11/2023	3	L84		1	30/11/2023	3	L84		1	30/11/2023	3	L84
5	2	30/11/2023	3	L85		2	30/11/2023	3	L85		2	30/11/2023	3	L85
6	3	30/11/2023	3	M85		3	30/11/2023	3	M85		3	30/11/2023	3	M85
7	4	30/11/2023	3	M84		4	30/11/2023	3	M84		4	30/11/2023	3	M84
8	5	1/12/2023	3	M84		5	1/12/2023	3	M84		5	#######	3	M84
9	6	1/12/2023	3	M85		6	1/12/2023	3	M85		6	#######	3	M85
10	7	1/12/2023	3	N84		7	1/12/2023	3	N84		7	#######	3	N84
11	8	1/12/2023	3	N85		8	1/12/2023	3	N85		8	#######	3	N85
12	9	5/12/2023	2	W47		9	5/12/2023	2	W47		9	#######	2	W47
13	10	5/12/2023	2	X46		10	5/12/2023	2	X46		10	#######	2	X46
14	11	5/12/2023	2	W47		11	5/12/2023	2	W47		11	#######	2	W47
15	12	5/12/2023	2	X46		12	5/12/2023	2	X46		12	#######	2	X46
16	13	5/12/2023	2	J86		13	5/12/2023	2	J86		13	#######	2	J86
17	14	5/12/2023	3	L85		14	5/12/2023	3	L85		14	#######	3	L85
18	15	5/12/2023	3	L84		15	5/12/2023	3	L84		15	#######	3	L84
19	16	5/12/2023	2	X48		16	5/12/2023	2	X48		16	#######	2	X48
20	17	5/12/2023	2	X46		17	5/12/2023	2	X46		17	#######	2	X46
21	18	5/12/2023	3	N85		18	5/12/2023	3	N85		18	#######	3	N85
22	19	5/12/2023	3	M84		19	5/12/2023	3	M84		19	#######	3	M84
23	20	11/12/2023	3	L86		20	11/12/2023	3	L86		20	#######	3	L86
24	21	11/12/2023	3	L85		21	11/12/2023	3	L85		21	#######	3	L85
25	22	11/12/2023	3	L85		22	11/12/2023	3	L85		22	#######	3	L85
26	23	11/12/2023	3	L85		23	11/12/2023	3	L85		23	#######	3	L85
27	24	11/12/2023	3	L86		24	11/12/2023	3	L86		24	#######	3	L86
28	25	11/12/2023	3	L86		25	11/12/2023	3	L86		25	#######	3	L86
29	26	12/12/2023	3	M86		26	12/12/2023	3	M86		26	#######	3	M86
30	27	12/12/2023	3	M86		27	12/12/2023	3	M86		27	#######	3	M86
31	28	18/12/2023	3	L86		28	18/12/2023	3	L86		28	18/12/2023	3	L86
32	29	18/12/2023	3	K86		29	18/12/2023	3	K86		29	18/12/2023	3	K86
33	30	18/12/2023	3	K85		30	18/12/2023	3	K85		30	18/12/2023	3	K85
34	31	18/12/2023	3	G89		31	18/12/2023	3	G89		31	18/12/2023	3	G89
35	32	18/12/2023	3	H85		32	18/12/2023	3	H85		32	18/12/2023	3	H85
36	33	18/12/2023	3	H86		33	18/12/2023	3	H86		33	18/12/2023	3	H86
37	34	18/12/2023	3	K85		34	18/12/2023	3	K85		34	18/12/2023	3	K85
38	35	18/12/2023	3	K86		35	18/12/2023	3	K86		35	18/12/2023	3	K86
39	36	9/1/2024	2	X43		36	9/1/2024	2	X43		36	9/1/2024	2	X43
40	37	9/1/2024	2	X44		37	9/1/2024	2	X44		37	9/1/2024	2	X44
41	38	9/1/2024	2	X43		38	9/1/2024	2	X43		38	9/1/2024	2	X43
42	39	9/1/2024	2	X44		39	9/1/2024	2	X44		39	9/1/2024	2	X44
43	40	9/1/2024	2	X44		40	9/1/2024	2	X44		40	9/1/2024	2	X44
44	41	9/1/2023	2	X43		41	9/1/2023	2	X43		41	9/1/2023	2	X43
45	42	10/1/2024	2	X44		42	10/1/2024	2	X44		42	#######	2	X44
46	43	10/1/2024	2	X43		43	10/1/2024	2	X43		43	#######	2	X43
47	44	10/1/2024	2	X44		44	10/1/2024	2	X44		44	#######	2	X44
48	45	10/1/2024	2	X43		45	10/1/2024	2	X43		45	#######	2	X43
49	46	10/1/2024	2	X43		46	10/1/2024	2	X43		46	#######	2	X43
50	47	10/1/2024	2	X44		47	10/1/2024	2	X44		47	#######	2	X44
51	48	10/1/2024	2	X44		48	10/1/2024	2	X44		48	#######	2	X44
52	49	10/1/2024	2	X43		49	10/1/2024	2	X43		49	#######	2	X43
53	50	11/1/2024	2	X42		50	11/1/2024	2	X42		50	#######	2	X42
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNT(UNIQUE(VSTACK(B4:B53,G4:G53,L4:L53)))

 

[Beagle263]

S7_BuiltinFunctions.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Testing Location					Testing Location					Testing Location
2																	Unique Count of Three Ranges
3	Test No.	Date Tested	Plot	Grid Square		Test No.	Date Tested	Plot	Grid Square		Test No.	Date Tested	Plot	Grid Square			8
4	1	30/11/2023	3	L84		1	30/11/2023	3	L84		1	30/11/2023	3	L84
5	2	30/11/2023	3	L85		2	30/11/2023	3	L85		2	30/11/2023	3	L85
6	3	30/11/2023	3	M85		3	30/11/2023	3	M85		3	30/11/2023	3	M85
7	4	30/11/2023	3	M84		4	30/11/2023	3	M84		4	30/11/2023	3	M84
8	5	1/12/2023	3	M84		5	1/12/2023	3	M84		5	#######	3	M84
9	6	1/12/2023	3	M85		6	1/12/2023	3	M85		6	#######	3	M85
10	7	1/12/2023	3	N84		7	1/12/2023	3	N84		7	#######	3	N84
11	8	1/12/2023	3	N85		8	1/12/2023	3	N85		8	#######	3	N85
12	9	5/12/2023	2	W47		9	5/12/2023	2	W47		9	#######	2	W47
13	10	5/12/2023	2	X46		10	5/12/2023	2	X46		10	#######	2	X46
14	11	5/12/2023	2	W47		11	5/12/2023	2	W47		11	#######	2	W47
15	12	5/12/2023	2	X46		12	5/12/2023	2	X46		12	#######	2	X46
16	13	5/12/2023	2	J86		13	5/12/2023	2	J86		13	#######	2	J86
17	14	5/12/2023	3	L85		14	5/12/2023	3	L85		14	#######	3	L85
18	15	5/12/2023	3	L84		15	5/12/2023	3	L84		15	#######	3	L84
19	16	5/12/2023	2	X48		16	5/12/2023	2	X48		16	#######	2	X48
20	17	5/12/2023	2	X46		17	5/12/2023	2	X46		17	#######	2	X46
21	18	5/12/2023	3	N85		18	5/12/2023	3	N85		18	#######	3	N85
22	19	5/12/2023	3	M84		19	5/12/2023	3	M84		19	#######	3	M84
23	20	11/12/2023	3	L86		20	11/12/2023	3	L86		20	#######	3	L86
24	21	11/12/2023	3	L85		21	11/12/2023	3	L85		21	#######	3	L85
25	22	11/12/2023	3	L85		22	11/12/2023	3	L85		22	#######	3	L85
26	23	11/12/2023	3	L85		23	11/12/2023	3	L85		23	#######	3	L85
27	24	11/12/2023	3	L86		24	11/12/2023	3	L86		24	#######	3	L86
28	25	11/12/2023	3	L86		25	11/12/2023	3	L86		25	#######	3	L86
29	26	12/12/2023	3	M86		26	12/12/2023	3	M86		26	#######	3	M86
30	27	12/12/2023	3	M86		27	12/12/2023	3	M86		27	#######	3	M86
31	28	18/12/2023	3	L86		28	18/12/2023	3	L86		28	18/12/2023	3	L86
32	29	18/12/2023	3	K86		29	18/12/2023	3	K86		29	18/12/2023	3	K86
33	30	18/12/2023	3	K85		30	18/12/2023	3	K85		30	18/12/2023	3	K85
34	31	18/12/2023	3	G89		31	18/12/2023	3	G89		31	18/12/2023	3	G89
35	32	18/12/2023	3	H85		32	18/12/2023	3	H85		32	18/12/2023	3	H85
36	33	18/12/2023	3	H86		33	18/12/2023	3	H86		33	18/12/2023	3	H86
37	34	18/12/2023	3	K85		34	18/12/2023	3	K85		34	18/12/2023	3	K85
38	35	18/12/2023	3	K86		35	18/12/2023	3	K86		35	18/12/2023	3	K86
39	36	9/1/2024	2	X43		36	9/1/2024	2	X43		36	9/1/2024	2	X43
40	37	9/1/2024	2	X44		37	9/1/2024	2	X44		37	9/1/2024	2	X44
41	38	9/1/2024	2	X43		38	9/1/2024	2	X43		38	9/1/2024	2	X43
42	39	9/1/2024	2	X44		39	9/1/2024	2	X44		39	9/1/2024	2	X44
43	40	9/1/2024	2	X44		40	9/1/2024	2	X44		40	9/1/2024	2	X44
44	41	9/1/2023	2	X43		41	9/1/2023	2	X43		41	9/1/2023	2	X43
45	42	10/1/2024	2	X44		42	10/1/2024	2	X44		42	#######	2	X44
46	43	10/1/2024	2	X43		43	10/1/2024	2	X43		43	#######	2	X43
47	44	10/1/2024	2	X44		44	10/1/2024	2	X44		44	#######	2	X44
48	45	10/1/2024	2	X43		45	10/1/2024	2	X43		45	#######	2	X43
49	46	10/1/2024	2	X43		46	10/1/2024	2	X43		46	#######	2	X43
50	47	10/1/2024	2	X44		47	10/1/2024	2	X44		47	#######	2	X44
51	48	10/1/2024	2	X44		48	10/1/2024	2	X44		48	#######	2	X44
52	49	10/1/2024	2	X43		49	10/1/2024	2	X43		49	#######	2	X43
53	50	11/1/2024	2	X42		50	11/1/2024	2	X42		50	#######	2	X42
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNT(UNIQUE(VSTACK(B4:B53,G4:G53,L4:L53)))

Thank you, I ended up using =COUNT(UNIQUE(TOCOL(v:v))))

I also want to see if I can then apply this to only count the dates from a given period, e.g from March 2024