Count distinct with conditions

[Uganda]

Hello, I would like to write a calculation using data in a table to know the number of distinct IDs which are there for each sector and company. My data is much larger than this example and I will have duplicate records (there are other columns which have different data in them).

Id	Sector	Company
123​	Sector A	Company 1
123​	Sector B	Company 1
234​	Sector A	Company 2
234​	Sector C	Company 2
234​	Sector A	Company 1
123​	Sector B	Company 1
567​	Sector C	Company 2

The result would be:

Sector	Company	Number
Sector A	Company 1	2​
Sector B	Company 1	1​
Sector A	Company 2	1​
Sector C	Company 2	2​

Sector B Company 1 appears twice but for the same ID so that is considered as 1 whereas Sector C Company 2 appears twice with different IDs so this counts as 2.
In SQL, I would do this as

select sector, company, count(distinct ID) from table
group by sector, company

If that helps - this is what I really want to do. I have searched and tried options using SUMPRODUCT and COUNTIFS but I couldn't seem to make it work.

Thanks in advance

 

[JvdV]

Here is one way assuming ms365:

Book1
	A	B	C	D	E	F	G
1	Id	Sector	Company
2	123	Sector A	Company 1		Sector A	Company 1	2
3	123	Sector B	Company 1		Sector B	Company 1	1
4	234	Sector A	Company 2		Sector A	Company 2	1
5	234	Sector C	Company 2		Sector C	Company 2	2
6	234	Sector A	Company 1
7	123	Sector B	Company 1
8	567	Sector C	Company 2
Sheet12

Cell Formulas
Range	Formula
E2:G5	E2	=LET(x,UNIQUE(B2:C8,0),HSTACK(x,BYROW(x,LAMBDA(z,COUNTA(UNIQUE(FILTER(A2:A8,MMULT(--(B2:C8=z),{1;1})=2)))))))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Uganda]

Here is one way assuming ms365:

Book1
	A	B	C	D	E	F	G
1	Id	Sector	Company
2	123	Sector A	Company 1		Sector A	Company 1	2
3	123	Sector B	Company 1		Sector B	Company 1	1
4	234	Sector A	Company 2		Sector A	Company 2	1
5	234	Sector C	Company 2		Sector C	Company 2	2
6	234	Sector A	Company 1
7	123	Sector B	Company 1
8	567	Sector C	Company 2
Sheet12

Cell Formulas
Range	Formula
E2:G5	E2	=LET(x,UNIQUE(B2:C8,0),HSTACK(x,BYROW(x,LAMBDA(z,COUNTA(UNIQUE(FILTER(A2:A8,MMULT(--(B2:C8=z),{1;1})=2)))))))
Dynamic array formulas.

Hello, thanks for your help - I did think it would be more straight forward. If I wanted to add a condition into this - imagine I have a flag in column D which is 1 or 0 and I want to them only count the rows with 1 in them. How could I adapt the array please?

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Id	Sector	Company
2	123	Sector A	Company 1		Sector A	Company 1	2
3	123	Sector B	Company 1		Sector B	Company 1	1
4	234	Sector A	Company 2		Sector A	Company 2	1
5	234	Sector C	Company 2		Sector C	Company 2	2
6	234	Sector A	Company 1
7	123	Sector B	Company 1
8	567	Sector C	Company 2
9
Lists

Cell Formulas
Range	Formula
G2:G5	G2	=ROWS(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=E2)*($C$2:$C$8=F2))))

 

[Uganda]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Id	Sector	Company
2	123	Sector A	Company 1		Sector A	Company 1	2
3	123	Sector B	Company 1		Sector B	Company 1	1
4	234	Sector A	Company 2		Sector A	Company 2	1
5	234	Sector C	Company 2		Sector C	Company 2	2
6	234	Sector A	Company 1
7	123	Sector B	Company 1
8	567	Sector C	Company 2
9
Lists

Cell Formulas
Range	Formula
G2:G5	G2	=ROWS(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=E2)*($C$2:$C$8=F2))))

Thank you so much for this. This is one of my tasks but I also will need to calculate the distinct IDs for each row that meet another criteria. E.g. if there is a 1 in column D, I will count it but not if there is a 0. How would I update the formula please?

Id	Sector	Company	Completed
123​	Sector A	Company 1	1​
123​	Sector B	Company 1	1​
234​	Sector A	Company 2	0​
234​	Sector C	Company 2	0​
234​	Sector A	Company 1	0​
123​	Sector B	Company 1	1​
567​	Sector C	Company 2	0​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Id	Sector	Company	Completed
2	123	Sector A	Company 1	1	Sector A	Company 1	1
3	123	Sector B	Company 1	1	Sector B	Company 1	0
4	234	Sector A	Company 2	0	Sector A	Company 2	0
5	234	Sector C	Company 2	0	Sector C	Company 2	0
6	234	Sector A	Company 1	0
7	123	Sector B	Company 1	1
8	567	Sector C	Company 2	0
9
Lists

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=E2)*($C$2:$C$8=F2)*(D2:D8=1)))),0)

 

[Uganda]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Id	Sector	Company	Completed
2	123	Sector A	Company 1	1	Sector A	Company 1	1
3	123	Sector B	Company 1	1	Sector B	Company 1	0
4	234	Sector A	Company 2	0	Sector A	Company 2	0
5	234	Sector C	Company 2	0	Sector C	Company 2	0
6	234	Sector A	Company 1	0
7	123	Sector B	Company 1	1
8	567	Sector C	Company 2	0
9
Lists

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=E2)*($C$2:$C$8=F2)*(D2:D8=1)))),0)

This is fantastic, thanks so much!

 

[Fluff]

Glad we could help & thanks for the feedback.