Counting no people working at a company

[mrsteele]

Hi all.

I have a list of contractors and companies they have worked for.
I need to calculate how many contractors have worked with this company within a data set.
Some of the contractors are charged weekly, so likely the names will be duplicated within this data set, so ideally need to exclude duplicates.
For example, have invoiced 1 contractor to the same client twice, so ideally, the answer to this would be 1 when the formula works.
The client and contractor names are within their own columns in a worksheet, with the information being displayed on a separate worksheet.

Thanks

 

[anand3dinesh]

Can you please share your data?

 

[mrsteele]

 

[mrsteele]

 

[mrsteele]

First picture is the data set, and the second is where i want the results being displayed.
So in this example, the answer to No of contractors for 'client 1', will be 2, for 'client 3' the answer is 1, and 'client 5' answer is also 1

 

[anand3dinesh]

I hope below link answers to your question

UNIQUE function - Microsoft Support

Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.

support.microsoft.com

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Client 1	Contractor 1	Sector 1			Sector 1
3	Client 2	Contractor 2	Sector 2				No Contractors
4	Client 3	Contractor 3	Sector 1			Client 1	2
5	Client 1	Contractor 4	Sector 1			Client 3	1
6	Client 4	Contractor 5	Sector 2			Client 5	1
7	Client 1	Contractor 1	Sector 1
8	Client 5	Contractor 6	Sector 1
9	Client 3	Contractor 3	Sector 1
10	Client 6	Contractor 7	Sector 2
11	Client 3	Contractor 3	Sector 1
12
Main

Cell Formulas
Range	Formula
F4:F6	F4	=UNIQUE(FILTER(A2:A11,C2:C11=F2))
G4:G6	G4	=ROWS(UNIQUE(FILTER($B$2:$B$11,($A$2:$A$11=F4)*($C$2:$C$11=$F$2))))
Dynamic array formulas.