Count unique values with 2 conditions

[esmarques]

Hello,
I am wondering if there is a way of counting unique values (per name on column F) in the table attached without applying any further filters as I will need it to stay at it is for other calculations.
So I would need to now for example, how many unique artitsts are from Bolton, from Bury etc... how many unique staff from Bolton, Bury, etc...

At the moment my formula is showing how many times each district appears per artist, staff etc but not considering if its the same name, so if same person signed in twice, it will count twice but I want it to count only once as its the same person.

Could anybody help?

Thank you

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	in	out	referrer	date	time	name	postcode	email	phone	reason	artist	staff	visitor	volunteer	participant	tutor	trades	delivery	audience	atendee	District	Month			Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	GM Non-Disttit Specific	TOTAL
2	sign in !		81.149.159.151	8/1/23	11:49:33	matt bennett	sk15 3lh							TRUE							Tameside	Aug-23		artist	1	0	0	0	0	0	0	0	0	0	0	1
3	sign in !		81.149.159.151	8/1/23	10:21:31	Becky Taylor-Christian	SK15 3BU							TRUE							Tameside	Aug-23		staff	0	0	5	0	0	0	0	4	0	0	0	9
4	sign in !		81.149.159.151	8/1/23	10:09:46	amelia bayliss	BL5 3SB				TRUE										Bolton	Aug-23		visitor	0	0	0	0	0	0	0	0	0	0	0	0
5	sign in !		81.149.159.151	9/3/23	9:28:24	Holly Prest	ol50dw					TRUE									Tameside	Sep-23		volunteer	0	0	0	0	0	0	0	2	0	0	0	2
6	sign in !		81.149.159.151	9/3/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Sep-23		participant	1	0	0	0	0	0	0	0	0	0	0	1
7	sign in !		81.149.159.151	10/15/23	9:13:18	Freya Bennett-Nielsen	SK15 3LH					TRUE									Tameside	Oct-23		tutor	0	0	0	0	0	0	0	0	0	0	0	0
8	sign in !		81.149.159.151	10/15/23	18:49:51	Alex Goulding	BL5 3SB								TRUE						Bolton	Oct-23		trades	0	0	0	0	0	0	0	0	0	0	0	0
9	sign in !		81.149.159.151	10/15/23	18:47:45	jon mambo	OL5 0QR					TRUE									Tameside	Oct-23		delivery	0	0	0	0	0	0	0	0	0	0	0	0
10	sign in !		81.149.159.151	10/15/23	10:31:10	leon patel	ol50sp					TRUE									Tameside	Oct-23		audience	0	0	0	0	0	0	0	0	0	0	0	0
11	sign in !		81.149.159.151	10/15/23	10:00:30	Ciara Bartlett	M4 4EE					TRUE									Manchester	Oct-23		atendee	0	0	0	0	0	0	0	0	0	0	0	0
12	sign in !		81.149.159.151	11/3/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23		TOTAL	2	0	5	0	0	0	0	6	0	0	0	13
13	sign in !		81.149.159.151	11/5/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23
14	sign in !		81.149.159.151	12/2/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Dec-23
15
Sheet1

Cell Formulas
Range	Formula
Y2:AH2	Y2	=COUNTIFS($K:$K,"TRUE",$U:$U,Y$1)
AI2	AI2	=COUNTIFS($K:$K,"TRUE",$U:$U,AI$40)
AJ2:AJ11	AJ2	=SUM(Y2:AI2)
Y3:AH3	Y3	=COUNTIFS($L:$L,"TRUE",$U:$U,Y$1)
AI3	AI3	=COUNTIFS($L:$L,"TRUE",$U:$U,AI$40)
Y4:AH4	Y4	=COUNTIFS($M:$M,"TRUE",$U:$U,Y$1)
AI4	AI4	=COUNTIFS($M:$M,"TRUE",$U:$U,AI$40)
Y5:AH5	Y5	=COUNTIFS($N:$N,"TRUE",$U:$U,Y$1)
AI5	AI5	=COUNTIFS($N:$N,"TRUE",$U:$U,AI$40)
Y6:AH6	Y6	=COUNTIFS($O:$O,"TRUE",$U:$U,Y$1)
AI6	AI6	=COUNTIFS($O:$O,"TRUE",$U:$U,AI$40)
Y7:AH7	Y7	=COUNTIFS($P:$P,"TRUE",$U:$U,Y$1)
AI7	AI7	=COUNTIFS($P:$P,"TRUE",$U:$U,AI$40)
Y8:AH8	Y8	=COUNTIFS($Q:$Q,"TRUE",$U:$U,Y$1)
AI8	AI8	=COUNTIFS($Q:$Q,"TRUE",$U:$U,AI$40)
Y9:AH9	Y9	=COUNTIFS($R:$R,"TRUE",$U:$U,Y$1)
AI9	AI9	=COUNTIFS($R:$R,"TRUE",$U:$U,AI$40)
Y10:AH10	Y10	=COUNTIFS($S:$S,"TRUE",$U:$U,Y$1)
AI10	AI10	=COUNTIFS($S:$S,"TRUE",$U:$U,AI$40)
Y11:AH11	Y11	=COUNTIFS($T:$T,"TRUE",$U:$U,Y$1)
AI11	AI11	=COUNTIFS($T:$T,"TRUE",$U:$U,AI$40)
Y12:AJ12	Y12	=SUM(Y2:Y9)
V2:V14	V2	=TEXT(D2,"mmm-YY")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	in	out	referrer	date	time	name	postcode	email	phone	reason	artist	staff	visitor	volunteer	participant	tutor	trades	delivery	audience	atendee	District	Month			Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	GM Non-Disttit Specific	TOTAL
2	sign in !		81.149.159.151	45139	0.49274306	matt bennett	sk15 3lh							TRUE							Tameside	Aug-23		artist	1	0	0	0	0	0	0	0	0	0	0	1
3	sign in !		81.149.159.151	45139	0.4316088	Becky Taylor-Christian	SK15 3BU							TRUE							Tameside	Aug-23		staff	0	0	2	0	0	0	0	4	0	0	0	6
4	sign in !		81.149.159.151	45139	0.42344907	amelia bayliss	BL5 3SB				TRUE										Bolton	Aug-23		visitor	0	0	0	0	0	0	0	0	0	0	0	0
5	sign in !		81.149.159.151	45172	0.39472222	Holly Prest	ol50dw					TRUE									Tameside	Sep-23		volunteer	0	0	0	0	0	0	0	2	0	0	0	2
6	sign in !		81.149.159.151	45172	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Sep-23		participant	1	0	0	0	0	0	0	0	0	0	0	1
7	sign in !		81.149.159.151	45214	0.38423611	Freya Bennett-Nielsen	SK15 3LH					TRUE									Tameside	Oct-23		tutor	0	0	0	0	0	0	0	0	0	0	0	0
8	sign in !		81.149.159.151	45214	0.78461806	Alex Goulding	BL5 3SB								TRUE						Bolton	Oct-23		trades	0	0	0	0	0	0	0	0	0	0	0	0
9	sign in !		81.149.159.151	45214	0.78315972	jon mambo	OL5 0QR					TRUE									Tameside	Oct-23		delivery	0	0	0	0	0	0	0	0	0	0	0	0
10	sign in !		81.149.159.151	45214	0.43831019	leon patel	ol50sp					TRUE									Tameside	Oct-23		audience	0	0	0	0	0	0	0	0	0	0	0	0
11	sign in !		81.149.159.151	45214	0.41701389	Ciara Bartlett	M4 4EE					TRUE									Manchester	Oct-23		atendee	0	0	0	0	0	0	0	0	0	0	0	0
12	sign in !		81.149.159.151	45233	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23		TOTAL	2	0	2	0	0	0	0	6	0	0	0	10
13	sign in !		81.149.159.151	45235	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23
14	sign in !		81.149.159.151	45262	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Dec-23
Data

Cell Formulas
Range	Formula
Y2:AI11	Y2	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$100,($U$2:$U$100=Y$1)*(INDEX($K$2:$T$100,,XMATCH($X2,$K$1:$T$1)))))),0)
AJ2:AJ11	AJ2	=SUM(Y2:AI2)
Y12:AJ12	Y12	=SUM(Y2:Y9)
V2:V14	V2	=TEXT(D2,"mmm-YY")

 

[esmarques]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	in	out	referrer	date	time	name	postcode	email	phone	reason	artist	staff	visitor	volunteer	participant	tutor	trades	delivery	audience	atendee	District	Month			Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	GM Non-Disttit Specific	TOTAL
2	sign in !		81.149.159.151	45139	0.49274306	matt bennett	sk15 3lh							TRUE							Tameside	Aug-23		artist	1	0	0	0	0	0	0	0	0	0	0	1
3	sign in !		81.149.159.151	45139	0.4316088	Becky Taylor-Christian	SK15 3BU							TRUE							Tameside	Aug-23		staff	0	0	2	0	0	0	0	4	0	0	0	6
4	sign in !		81.149.159.151	45139	0.42344907	amelia bayliss	BL5 3SB				TRUE										Bolton	Aug-23		visitor	0	0	0	0	0	0	0	0	0	0	0	0
5	sign in !		81.149.159.151	45172	0.39472222	Holly Prest	ol50dw					TRUE									Tameside	Sep-23		volunteer	0	0	0	0	0	0	0	2	0	0	0	2
6	sign in !		81.149.159.151	45172	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Sep-23		participant	1	0	0	0	0	0	0	0	0	0	0	1
7	sign in !		81.149.159.151	45214	0.38423611	Freya Bennett-Nielsen	SK15 3LH					TRUE									Tameside	Oct-23		tutor	0	0	0	0	0	0	0	0	0	0	0	0
8	sign in !		81.149.159.151	45214	0.78461806	Alex Goulding	BL5 3SB								TRUE						Bolton	Oct-23		trades	0	0	0	0	0	0	0	0	0	0	0	0
9	sign in !		81.149.159.151	45214	0.78315972	jon mambo	OL5 0QR					TRUE									Tameside	Oct-23		delivery	0	0	0	0	0	0	0	0	0	0	0	0
10	sign in !		81.149.159.151	45214	0.43831019	leon patel	ol50sp					TRUE									Tameside	Oct-23		audience	0	0	0	0	0	0	0	0	0	0	0	0
11	sign in !		81.149.159.151	45214	0.41701389	Ciara Bartlett	M4 4EE					TRUE									Manchester	Oct-23		atendee	0	0	0	0	0	0	0	0	0	0	0	0
12	sign in !		81.149.159.151	45233	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23		TOTAL	2	0	2	0	0	0	0	6	0	0	0	10
13	sign in !		81.149.159.151	45235	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23
14	sign in !		81.149.159.151	45262	0.38435185	Eraldo Marques	M125LR					TRUE									Manchester	Dec-23
Data

Cell Formulas
Range	Formula
Y2:AI11	Y2	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$100,($U$2:$U$100=Y$1)*(INDEX($K$2:$T$100,,XMATCH($X2,$K$1:$T$1)))))),0)
AJ2:AJ11	AJ2	=SUM(Y2:AI2)
Y12:AJ12	Y12	=SUM(Y2:Y9)
V2:V14	V2	=TEXT(D2,"mmm-YY")

Hello,
This worked perfectly. Thank you very very much!
One thing I didnt' realise is if someone don't enter any data about if they are artist, staff etc. In this updated table below, would you know how to calculate X12:AI12 in yellow ?

Thank you!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	in	out	referrer	date	time	name	postcode	email	phone	reason	artist	staff	visitor	volunteer	participant	tutor	trades	delivery	audience	atendee	District	Month			Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	GM Non-Disttit Specific	Not found	TOTAL
2	sign in !		81.149.159.151	8/1/23	11:49:33	matt bennett	sk15 3lh							TRUE							Tameside	Aug-23		artist	1	0	0	0	0	0	0	0	0	0	0	0	1
3	sign in !		81.149.159.151	8/1/23	10:21:31	Becky Taylor-Christian								TRUE							Not found	Aug-23		staff	0	0	2	0	0	0	0	2	0	0	0	1	5
4	sign in !		81.149.159.151	8/1/23	10:09:46	amelia bayliss	BL5 3SB				TRUE										Bolton	Aug-23		visitor	0	0	0	0	0	0	0	0	0	0	0	0	0
5	sign in !		81.149.159.151	9/3/23	9:28:24	Holly Prest	ol50dw														Tameside	Sep-23		volunteer	0	0	0	0	0	0	0	1	0	0	0	1	2
6	sign in !		81.149.159.151	9/3/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Sep-23		participant	1	0	0	0	0	0	0	0	0	0	0	0	1
7	sign in !		81.149.159.151	10/15/23	9:13:18	Freya Bennett-Nielsen	SK15 3LH					TRUE									Tameside	Oct-23		tutor	0	0	0	0	0	0	0	0	0	0	0	0	0
8	sign in !		81.149.159.151	10/15/23	18:49:51	Alex Goulding	BL5 3SB								TRUE						Bolton	Oct-23		trades	0	0	0	0	0	0	0	0	0	0	0	0	0
9	sign in !		81.149.159.151	10/15/23	18:47:45	jon mambo	OL5 0QR					TRUE									Tameside	Oct-23		delivery	0	0	0	0	0	0	0	0	0	0	0	0	0
10	sign in !		81.149.159.151	10/15/23	10:31:10	leon patel						TRUE									Not found	Oct-23		audience	0	0	0	0	0	0	0	0	0	0	0	0	0
11	sign in !		81.149.159.151	10/15/23	10:00:30	Ciara Bartlett	M4 4EE					TRUE									Manchester	Oct-23		atendee	0	0	0	0	0	0	0	0	0	0	0	0	0
12	sign in !		81.149.159.151	11/3/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23		No data													0
13	sign in !		81.149.159.151	11/5/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Nov-23		TOTAL	2	0	2	0	0	0	0	3	0	0	0	2	9
14	sign in !		81.149.159.151	12/2/23	9:13:28	Eraldo Marques	M125LR					TRUE									Manchester	Dec-23
Sheet1

Cell Formulas
Range	Formula
Y2:AJ11	Y2	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$100,($U$2:$U$100=Y$1)*(INDEX($K$2:$T$100,,XMATCH($X2,$K$1:$T$1)))))),0)
AK2:AK12	AK2	=SUM(Y2:AJ2)
Y13:AK13	Y13	=SUM(Y2:Y9)
V2:V14	V2	=TEXT(D2,"mmm-YY")

 

[Fluff]

What are your expected results for that row?

 

[esmarques]

What are your expected results for that row?

All zero but AF12 = 1 ( someome from Tameside but no data entered about if they were staff, participant, etc )

 

[esmarques]

All zero but AF12 = 1 ( someome from Tameside but no data entered about if they were staff, participant, etc )

So unique values(per name on column F) that didn't entered if they were artists, participants, etc... so all cells from K:T for that specific person would be blank...

 

[Fluff]

Ok how about

=SUM(--(MMULT(($U$2:$U$100=Y1)*($K$2:$T$100=""),SEQUENCE(10,,,0))=10))

 

[esmarques]

Ok how about

=SUM(--(MMULT(($U$2:$U$100=Y1)*($K$2:$T$100=""),SEQUENCE(10,,,0))=10))

Absolute magic. Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[esmarques]

Hello,
Sorry to bother again, and please let me know if its better to start a new thread but its just one last column on this same doc that I am struggling to find the formula.
This would be to count those unique names from other districs other than the ones given or not found (column AJ). So in the table if you see on line 10, there is a tutor from Chesire East, but its not in the required specific Districts, its not counting him.
Please let me know if you need any further queries or if its better to start a new thread.

Thank you very much...

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	in	out	referrer	date	time	name	postcode	email	phone	reason	artist	staff	visitor	volunteer	participant	tutor	trades	delivery	audience	atendee	District	Month			Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	Not Found	Users Outside GM	TOTAL
2		out	81.149.159.151	9/30/23	16:11:51	Jon						TRUE									Tameside	Sep-23		artist	0	0	0	0	0	0	0	1	0	0	0		1
3		out	81.149.159.151	9/30/23	12:54:15	Martha					TRUE										Tameside	Sep-23		staff	0	0	1	0	0	0	0	1	0	0	0		2
4	in		81.149.159.151	9/30/23	10:21:49	Jayne										TRUE					Tameside	Sep-23		visitor	0	0	0	0	0	0	0	2	0	0	0		2
5		out	81.149.159.151	9/29/23	17:43:04	Amelia						TRUE									Not found	Sep-23		volunteer	0	0	0	0	0	0	0	0	0	0	0		0
6		out	81.149.159.151	9/29/23	12:39:23	Jude								TRUE							Kirklees	Sep-23		participant	0	0	0	0	0	0	0	0	0	0	0		0
7		out	81.149.159.151	9/29/23	11:52:27	Melanie															Tameside	Sep-23		tutor	0	0	0	0	0	0	0	1	0	0	0		1
8	in		81.149.159.151	9/29/23	11:18:10	Dan						TRUE									Not found	Sep-23		trades	0	0	0	0	0	0	0	0	0	0	0		0
9	in		81.149.159.151	9/29/23	10:12:31	Eraldo						TRUE									Manchester	Sep-23		delivery	0	0	0	0	0	0	0	0	0	0	0		0
10		out	81.149.159.151	9/20/23	20:57:41	Jack					TRUE										Cheshire East	Sep-23		audience	0	0	0	0	0	0	0	0	0	0	0		0
11		out	81.149.159.151	9/19/23	21:45:03	Richard							TRUE								Tameside	Sep-23		atendee	0	0	0	0	0	0	0	0	0	0	0		0
12		out	81.149.159.151	9/19/23	21:44:48	Sian							TRUE								Tameside	Sep-23		No Data	0	0	0	0	0	0	0	1	0	0	0		1
13	sign in !			03/07/23	19:00	Jack										TRUE					Cheshire East	Jul-23		TOTAL	0	0	1	0	0	0	0	5	0	0	0	0	6
14	sign in !			03/07/23	19:00	Jack										TRUE					Cheshire East	Jul-23
Sheet1

Cell Formulas
Range	Formula
Y2:AH11	Y2	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$1000,($U$2:$U$1000=Y$1)*(INDEX($K$2:$T$1000,,XMATCH($X2,$K$1:$T$1)))))),0)
AI2:AI11	AI2	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$1000,($U$2:$U$1000=AI$40)*(INDEX($K$2:$T$1000,,XMATCH($X2,$K$1:$T$1)))))),0)
Y12:AI12	Y12	=SUM(--(MMULT(($U$2:$U$1000=Y1)*($K$2:$T$1000=""),SEQUENCE(10,,,0))=10))
AK2:AK12	AK2	=SUM(Y2:AJ2)
Y13:AK13	Y13	=SUM(Y2:Y9)
V2:V14	V2	=TEXT(D2,"mmm-YY")