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

 

[Peter_SSs]

Should be able to multiply as many column conditions as you want.

 

[Wishmaster89]

Hi, i have a related question to my original post.

Is it possible to use the Count(Unique(Filter combination to count unique ID's except where the course is different?
E.g. ID 1 and 4 will be counted twice as they have 2 different courses

Book1
	A	B	C	D
1	ID	Course	year	status
2	1	History	17/18	1
3	2	Sports	17/18	2
4	3	IT	17/18	1
5	4	English	17/18	3
6	5	History	17/18	6
7	1	IT	17/18	2
8	2	Sports	17/18	3
9	4	Math	17/18	1
Sheet1

 

[Wishmaster89]

Ignore above, this is a better example.

So here i usually use Count(unique(filter which works great. However on the bottom 3 rows, the start year (L), ID(A) and status (C) are the same but the standard_description is different so i want this ID to be counted twice instead of only once which Count(unique(filter will give. The reason i want to keep the filter in is so i can check other criteria such as status(B), months(L) ect. Unless theres another way to do it?

Book7
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Completion_Status	Learning_Outcome	Standard_Description	Learning_Start_Date	Learning_Planned_End_Date	Learning_Actual_End_Date	Withdrawal days	Months	Min start	Min start year	Last row of id/course
2	1	1	9	Insurance Practitioner	10/04/2018 00:00	09/10/2019 00:00				10/04/2018	17/18
3	1	3	3	Business Administrator	08/01/2018 00:00	07/04/2019 00:00	07/02/2018 00:00	30	0-6	08/01/2018	17/18
4	1	3	3	Insurance Practitioner	10/04/2018 00:00	09/10/2019 00:00	29/08/2018 00:00	141	0-6	10/04/2018	17/18	1
5	1	3	3	Business Administrator	08/01/2018 00:00	07/04/2019 00:00	07/02/2018 00:00	30	0-6	08/01/2018	17/18	1
Sheet1

 

[Peter_SSs]

See if this is what you mean. (Check Table name)

Wishmaster89.xlsm
	A			D					I		K		M	N
1	ID			Standard_Description					Months		Min start year
2	1			Insurance Practitioner							17/18			2
3	1			Business Administrator					0-6		17/18
4	1			Insurance Practitioner					0-6		17/18
5	1			Business Administrator					0-6		17/18
Sheet1

Cell Formulas
Range	Formula
N2	N2	=IFNA(ROWS(UNIQUE(FILTER(Table1[ID]&"|"&Table1[Standard_Description],(Table1[Min start year]="17/18")*(Table1[Months]="0-6"),NA()))),0)

 

[Wishmaster89]

See if this is what you mean. (Check Table name)

Wishmaster89.xlsm
	A			D					I		K		M	N
1	ID			Standard_Description					Months		Min start year
2	1			Insurance Practitioner							17/18			2
3	1			Business Administrator					0-6		17/18
4	1			Insurance Practitioner					0-6		17/18
5	1			Business Administrator					0-6		17/18
Sheet1

Cell Formulas
Range	Formula
N2	N2	=IFNA(ROWS(UNIQUE(FILTER(Table1[ID]&"|"&Table1[Standard_Description],(Table1[Min start year]="17/18")*(Table1[Months]="0-6"),NA()))),0)

Yep that works thank you.

 

[Wishmaster89]

See if this is what you mean. (Check Table name)

Wishmaster89.xlsm
	A			D					I		K		M	N
1	ID			Standard_Description					Months		Min start year
2	1			Insurance Practitioner							17/18			2
3	1			Business Administrator					0-6		17/18
4	1			Insurance Practitioner					0-6		17/18
5	1			Business Administrator					0-6		17/18
Sheet1

Cell Formulas
Range	Formula
N2	N2	=IFNA(ROWS(UNIQUE(FILTER(Table1[ID]&"|"&Table1[Standard_Description],(Table1[Min start year]="17/18")*(Table1[Months]="0-6"),NA()))),0)

I've been playing round with the formula and have the following question. I know & concatenates two fields but what does | do please?

 

[Peter_SSs]

Yep that works thank you.

Good news. Thanks for the confirmation.

I know & concatenates two fields but what does | do please?

It is a safety mechanism to ensure that you are actually comparing IDs & Descriptions.
With the samples below, using the "|" you get
"1c|hop"
"1|chop"
Rightly, these produce two unique ID/Description combinations.

If we did not use the "|" to delimit the columns then the concatenation would give only 1 unique combination.
"1chop"
"1chop"

Wishmaster89.xlsm
	A	B
1	ID	Description
2	1c	hop
3	1	chop
Sheet3

 

[Wishmaster89]

Good news. Thanks for the confirmation.


It is a safety mechanism to ensure that you are actually comparing IDs & Descriptions.
With the samples below, using the "|" you get
"1c|hop"
"1|chop"
Rightly, these produce two unique ID/Description combinations.

If we did not use the "|" to delimit the columns then the concatenation would give only 1 unique combination.
"1chop"
"1chop"

Wishmaster89.xlsm
	A	B
1	ID	Description
2	1c	hop
3	1	chop
Sheet3

Ah yes of course thanks. Its made me think about how i do index match with more than 1 look up value, usually i just use & and it does give the correct answer. But 'm now thinking i should add in the |.

 

[Peter_SSs]

But 'm now thinking i should add in the |.

Sometimes it would not be needed. For example if you know your data well and say the first column is names and the second column is dates then the sort of issue I mentioned could not arise.
However, it should never hurt to include such a delimiter - which can be anything so long as it is not going to already occur naturally within any of the data.