Count unique values across two columns

[Wishmaster89]

Hi All

I am trying to count the number of ID's that start in 2017. I know you can do sumproduct with count if , but i couldnt't get it working with countifs.

Sample data below.

QAR withdrawal comparison.xlsx
	A	B
1	ID	Start_Year
2	1	2017
3	2	2017
4	3	2017
5	4	2017
6	5	2018
7	6	2017
8	7	2017
9	1	2018
10	9	2017
11	10	2017
12	5	2018
13	11	2017
14	12	2017
15	13	2017
16	9	2018
Sheet1

 

[bebo021999]

=SUMPRODUCT(IF($B$2:$B$16=2017,1/COUNTIFS($B$2:$B$16,2017,$A$2:$A$16,$A$2:$A$16),""))

For Ex2016 or earlier, array formula requires Ctrl-Shift-Enter combination, instead of Enter

 

[Peter_SSs]

Assuming that your ID's are numerical like your samples and that you are happy to be using your 2021 version

=COUNT(UNIQUE(FILTER(A2:A16,B2:B16=2017)))

 

[Wishmaster89]

Thank you so much, they both worked. I do have a follow up question please.

On the sample data A-C is one tab and G-I is another but to make this example easier i've put them on one sheet.

I need to count anyone from A-C and G-I that have a start year of 2017 but I don't want to double count people. E.g, ID 1 is in both data sets but ID 33 is only in the second data set so he would be counted.

Book2
	A	B	C	D	E	F	G	H	I
1	ID	Start_Year	Status				ID	Start_Year	Status
2	1	2017	1				1	2017	1
3	2	2017	1				2	2017	1
4	3	2017	3				3	2017	3
5	4	2017	1				4	2017	1
6	5	2018	1				5	2018	1
7	6	2017	1				6	2017	1
8	7	2017	1				7	2017	1
9	27	2018	1				27	2018	1
10	9	2017	1				9	2017	1
11	10	2017	1				10	2017	1
12	5	2018	6				5	2018	1
13	11	2017	1				11	2017	1
14	12	2017	1				12	2017	1
15	13	2017	1				13	2017	1
16	9	2018	6				9	2018	1
17	1	2017	6				1	2017	1
18	3	2017	3				3	2017	1
19	900	2017	6				900	2017	1
20							33	2017	1
Sheet1

 

[Peter_SSs]

Do you happen to have the VSTACK function in your 2021 version?
A couple of options depending on the answer.
I have done it on two sheets

22 08 11.xlsm
	A	B	C
1	ID	Start_Year	Status
2	1	2017	1
3	2	2017	1
4	3	2017	3
5	4	2017	1
6	5	2018	1
7	6	2017	1
8	7	2017	1
9	27	2018	1
10	9	2017	1
11	10	2017	1
12	5	2018	6
13	11	2017	1
14	12	2017	1
15	13	2017	1
16	9	2018	6
17	1	2017	6
18	3	2017	3
19	900	2017	6
Sht1

22 08 11.xlsm
	A	B	C	D	E	F
1	ID	Start_Year	Status		VSTACK	NO VSTACK
2	1	2017	1		13	13
3	2	2017	1
4	3	2017	3
5	4	2017	1
6	5	2018	1
7	6	2017	1
8	7	2017	1
9	27	2018	1
10	9	2017	1
11	10	2017	1
12	5	2018	1
13	11	2017	1
14	12	2017	1
15	13	2017	1
16	9	2018	1
17	1	2017	1
18	3	2017	1
19	900	2017	1
20	33	2017	1
Sht2

Cell Formulas
Range	Formula
E2	E2	=LET(vs,VSTACK('Sht1'!A2:B19,A2:B20),COUNT(UNIQUE(FILTER(INDEX(vs,0,1),INDEX(vs,0,2)=2017))))
F2	F2	=COUNT(UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF('Sht1'!B2:B19=2017,'Sht1'!A2:A19,""),IF(B2:B20=2017,A2:A20,""))&"</c></p>","//c")))

 

[Wishmaster89]

I had to re-edit my last post as there was some actual learner ID's that i didn't feel comfortable sharing.

Annoyingly my answer to Peter_SSs also dissapeared. I do have excel 365 but when typing in =VSTACK nothing appeared, not sure why. Anyway, i have put all of the tabs into one sheet to make it a bit easier. What i need to do now is count unique ID's that have Completion Status(B)=3 and Start Year(D)=2017. I have tried the formula in H2 but the result shows as 0.

QAR withdrawal comparison.xlsx
	A	B	C	D	E	F	G	H
1	ID	Completion Status	Start Date	Start Year	Learning_Actual_End_Date converted	Withdrawal time frame(days - if applcable)	Months	Status= 3 in 7-12 months
2	1	6	26/07/2017	2016	19/04/2018	267	7-12	0
3	2	3	17/01/2018	2017	04/07/2018	168	0-6
4	3	6	05/07/2017	2016	08/11/2017	126	0-6
5	4	3	28/11/2017	2017	15/01/2018	48	0-6
6	5	3	11/07/2017	2016	04/09/2017	55	0-6
7	6	3	24/07/2017	2016	18/04/2018	268	7-12
8	7	3	27/07/2017	2016	31/10/2017	96	0-6
9	8	6	27/07/2017	2016	22/07/2018	360	7-12
10	9	6	11/07/2017	2016	12/02/2018	216	7-12
11	10	3	14/06/2017	2016	16/08/2017	63	0-6
12	11	6	23/02/2018	2017	24/07/2018	151	0-6
13	12	3	08/02/2018	2017	13/06/2018	125	0-6
14	13	6	20/09/2017	2017
15	14	6	07/07/2017	2016	21/02/2018	229	7-12
16	15	6	06/07/2017	2016	18/06/2018	347	7-12
17	16	6	07/07/2017	2016	21/11/2017	137	0-6
18	17	6	02/06/2017	2016	18/06/2018	381	not in a category
19	18	3	19/06/2017	2016	17/04/2018	302	7-12
20	19	3	18/05/2017	2016	16/03/2018	302	7-12
21	20	3	27/07/2017	2016	06/09/2017	41	0-6
22	21	3	09/06/2017	2016	23/08/2017	75	0-6
23	22	3	23/06/2017	2016	02/07/2018	374	not in a category
24	23	3	23/06/2017	2016	16/05/2018	327	7-12
25	24	3	05/07/2017	2016	08/11/2017	126	0-6
26	25	3	11/07/2017	2016	12/03/2018	244	7-12
27	26	3	27/06/2017	2016	16/11/2017	142	0-6
28	27	1	16/11/2016	2016
29	4	3	28/11/2017	2017	15/01/2018	48	0-6
30	5	3	11/07/2017	2016	04/09/2017	55	0-6
31	6	3	24/07/2017	2016	18/04/2018	268	7-12
Sheet2

Cell Formulas
Range	Formula
H2	H2	=COUNT(UNIQUE(FILTER('Raw data'!$A$2:$A$31,($D$2:$D$5105=2017)*('Raw data'!$G$2:$G$5105=7-12))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A29:A31	Cell Value	duplicates	text	NO
A2:A27	Cell Value	duplicates	text	NO

 

[Peter_SSs]

when typing in =VSTACK nothing appeared, not sure why

Because it is only gradually being rolled out to some users until M/Soft is sure that is is robust enough to distribute to everybody.

I have tried the formula in H2 but the result shows as 0

Can't help with that as it refers to sheet 'Raw data' that we cannot see.

For the sample data that you have shown, try

=COUNT(UNIQUE(FILTER(A2:A31,(B2:B31=3)*(D2:D31=2017))))

 

[Wishmaster89]

Because it is only gradually being rolled out to some users until M/Soft is sure that is is robust enough to distribute to everybody.



Can't help with that as it refers to sheet 'Raw data' that we cannot see.

For the sample data that you have shown, try

=COUNT(UNIQUE(FILTER(A2:A31,(B2:B31=3)*(D2:D31=2017))))

Thank you so much! Ah i thought i had changed the formula for the sample sheet not to include the raw data sheet reference. I must be going mad, before i asked on the forum i was googling and thinking of all sorts of ways to try and tackle this.

Thanks again.

 

[Peter_SSs]

You're welcome. Glad it worked for you.

 

[Wishmaster89]

Is there a limit to how many columns i can add in to the * part of the formula below?

=COUNT(UNIQUE(FILTER(A2:A31,(B2:B31=3)*(D2:D31=2017))))