Counting unique numbers in a column based on data in another column

This is a partial of a much larger spreadsheet, I have omitted non related data to protect my companies privacy
On the spreadsheet, sometimes I have multiple rows with the same job name and same job number, I have used sumif frequency to get the total amount of jobs which works out perfectly. But I also want a relationship of total job numbers (unique number) for each draftsman

Can anyone help? I don't really know what to search for on the web to get the answer I need

Thanks

What are your expected outcomes in the red cells?

AliGW said:

What are your expected outcomes in the red cells?

Click to expand...

Hi Ali,

Thanks for responding basically I know that there are 39 unique numbers in column A. However those numbers repeat with the same drafter assigned.
I already know that under Vince there are 32 projects and under David there are 7 projects (total 39). But as time goes on my list keeps building, new jobs keep getting added and I want this list to be self regulated so all I have to do is have a glance at it for figures.

Under the total of 39, I'm using a sum(if(frequency to get the total of unique numbers under that column (10,000 entries). But for the life of me I cant figure out how to get a sumif based on a single name from column B

***edit i.e for the red cell for Vince I want to do a sum(if(frequency to count all the unique numbers in column a if column b has "Vince" in it

Yes, I get that, but what numbers are you expecting to see in the two red cells, please?

32 for Vince
7 for David

Try this (these are array formula entered using CTRL+SHIFT+ENTER):

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	Job No.​	DRAFTER​		Jobs​	Drafter​
2​				39​	VINCE​	32​
3​					DAVID​	7​
4​	0369​	VINCE​
5​	0368​	VINCE​
6​	0354​	VINCE​
7​	0367​	VINCE​
8​	0366​	VINCE​
9​	0365​	VINCE​
10​	0364​	VINCE​
11​	0363​	DAVID​
12​	0362​	VINCE​
13​	0362​	VINCE​
14​	0362​	VINCE​

Excel 2016 (Windows) 32 bit D​ E​ F​ 1​ Jobs​ Drafter​ 2​ =SUM(IF(FREQUENCY(A3:A10041,A3:A10041)>0,1))​ VINCE​ =SUM(IF(E2=$B$4:$B$10041,1/(COUNTIFS($B$4:$B$10041,E2,$A$4:$A$10041,$A$4:$A$10041)),0))​ 3​ DAVID​ =SUM(IF(E3=$B$4:$B$10041,1/(COUNTIFS($B$4:$B$10041,E3,$A$4:$A$10041,$A$4:$A$10041)),0))​ Sheet: Sheet1

Thank you so much, however I think I'm doing something wrong, I get "0" as results for both. The cells are AB57 & AB58
I have re-attached my file and I have posted all the data from the full sheet. I have just removed the sensitive information

You have not entered the formulae correctly. You must click on CTRL+SHIFT+ENTER to confirm the formulae, not just ENTER.

I love you.
Thank you very much works perfectly

Thanks for your help and time

I didn't realise this was an array

You are very welcome! Glad to have helped.