How to display a number of contacts per company

[jchrisphonte]

Hello All,

This one might be pretty simple but sadly i still dont know how to do it...
On sheet 1 of my excel sheet i have a list of companies and they all have a unique identifying number... On sheet two are contacts that work at these companies and they each are connected to a compnay on shhet 1 -some companies have no contacts and some have up to 200...

Here's what I'm trying to do - I'd just like to make a new column on sheet 1 that tells me how many contacts exist for that company.

heres an image of sheet 1 and 2 to visualize,

Sheet 1 - Companies

Excel Workbook
	A	B
9	555	36ONE Asset Management (Pty) Ltd
10	544	3A Alternative Asset Advisors (Syz & Co Bank)
11	556	3C Asset Management
12	4	3G Capital Management LLC
Funds

Sheet 2 Contacts

Excel Workbook
	B	C	D	E	F	G
13	555	36ONE Asset Management (Pty) Ltd	Ms.	Slatter	Laurie	Principle/Associate
14	555	36ONE Asset Management (Pty) Ltd	Mr.	Jacobs	Cy	Fund Manager
15	555	36ONE Asset Management (Pty) Ltd	Mr.	Liptz	Steven	Fund Manager
16	555	36ONE Asset Management (Pty) Ltd	Ms.	De Abreau	Tanya	Fund Manager
17	555	36ONE Asset Management (Pty) Ltd	Mr.	Seaborn	Ryan	Principle/Associate
18	555	36ONE Asset Management (Pty) Ltd	Mr.	Isaacs	Daniel	Management Consultant
19	544	3A Alternative Asset Advisors (Syz & Co Bank)	Mr.	Van Rijckevorsel	Cedric	Senior Analyst
20	544	3A Alternative Asset Advisors (Syz & Co Bank)	Mr.	Keller	Jean	Chief Executive Officer
21	544	3A Alternative Asset Advisors (Syz & Co Bank)	Mr.	Galeano	Jose	Chief Investment Officer
22	544	3A Alternative Asset Advisors (Syz & Co Bank)	Ms.	Maire	Sophie	Principle/Associate
23	544	3A Alternative Asset Advisors (Syz & Co Bank)	Ms.	Panayiotopoulos	Marie-Claire	Principle/Associate
24	544	3A Alternative Asset Advisors (Syz & Co Bank)	Mr.	Malquarti	Michael	Principle/Associate
25	544	3A Alternative Asset Advisors (Syz & Co Bank)	Ms.	De Piante	Michelle	Analyst/PM
26	556	3C Asset Management	Ms.	Heikkil	Juhana	Portfolio Manager
27	556	3C Asset Management	Mr.	Manninen	Reijo	Fund Manager
28	556	3C Asset Management	Mr.	Kurki-Suonio	Seppo	Principle/Associate
29	556	3C Asset Management	Mr.	Bystedt	Tom	Principle/Associate
30	556	3C Asset Management	Ms.	Heikkil	Juhana	Portfolio Manager
31	556	3C Asset Management	Mr.	Laaksonen	Kimmo	Board Member
32	556	3C Asset Management	Mr.	Leinonen	Jorma	Board Member
33	556	3C Asset Management	Mr.	Mantila	Antti	Chairman
34	556	3C Asset Management	Mr.	Leijala	Aku	CEO
35	556	3C Asset Management		Greensmith	David*	Regional Director
36	556	3C Asset Management		Manjdadria	Yogeeta*	Administrator
37	556	3C Asset Management		Rhodes	Ian*	Regional Manager
38	556	3C Asset Management		Judge	Indi*	IT Support
39	556	3C Asset Management		Strevens	Michael*	Unit Manager
40	556	3C Asset Management		Riutta	Pivi*	Portfolio Manager
41	556	3C Asset Management		Hogg*	Caroline*	Marketing Manager
42	556	3C Asset Management		Kurki-Suonio	Seppo	Portfolio Manager, Partner
43	556	3C Asset Management		Dhonoa	Raj	IT Manager
44	556	3C Asset Management		Dunman	Jon	Managing Director
45	556	3C Asset Management		Elomaa	Juhani	Managing Director
46	556	3C Asset Management		Irvine	Robert	Unit Manager
47	556	3C Asset Management		Lundberg	Arne	Partner
48	556	3C Asset Management		Gaston	Jane	Regional Manager
49	4	3G Capital Management LLC	Mr.	Behring	Alex	CEO
50	4	3G Capital Management LLC	Mr.	Piquet	Bernardo	Principle/Associate
51	4	3G Capital Management LLC	Ms.	Botto	Caroline	Principle/Associate
52	4	3G Capital Management LLC	Ms.	Lee	Ona	Principle/Associate
53	4	3G Capital Management LLC	Mr.	Bailey	Cory	Co-Founder
54	4	3G Capital Management LLC	Mr.	Begun	Pavel	Co-Founder
55	4	3G Capital Management LLC		Newman	Eric*	Associate
56	4	3G Capital Management LLC		McIntyre	Michele*	Assistant
57	4	3G Capital Management LLC		Frame	Matt*	Analyst
58	4	3G Capital Management LLC		Das	Denise*	office manager
59	4	3G Capital Management LLC		Pandya	Marshal	Associate
60	4	3G Capital Management LLC		Drevon	Pedro*	Analyst
61	4	3G Capital Management LLC		Luijpers	Patrick*	Technologist
62	4	3G Capital Management LLC		Goodwin	Jeremy**	Managing Partner
63	4	3G Capital Management LLC		Taradash	Reid	Analyst
64	4	3G Capital Management LLC		Liu	Alfred	Vice-President
65	4	3G Capital Management LLC		Thompson	Roberto	Owner
66	4	3G Capital Management LLC		Jajoo	Subir	Analyst
67	4	3G Capital Management LLC		Lo	Tyson	Analyst
68	4	3G Capital Management LLC		David	Jessica	Associate
69	4	3G Capital Management LLC		Cullinan	Derk	Partner
70	4	3G Capital Management LLC		Krieger	Ryan	Analyst
71	4	3G Capital Management LLC		Mazetto	Giovano	Associate
Contacts

 

[texasalynn]

try this

Excel Workbook
	A	B	C
8	Count of contacts	*	*
9	6	555	36ONE Asset Management (Pty) Ltd
10	*	544	3A Alternative Asset Advisors (Syz & Co Bank)
11	*	556	3C Asset Management
12	*	4	3G Capital Management LLC
Sheet1

then copy the formula down

 

[jchrisphonte]

Hi,

Thanks for the reply - The result you posted is exactly what I'm looking for.. after i copied it into the cell i got this in the folmula bar -

=COUNTIF(Sheet2!B$1:B$65536,Sheet1!B)

how should i amend it to work?

first company number is in A$2 and first contact company number is in B$2

thank you very much for your help...

Jerry

 

[jchrisphonte]

i found the problem -my sheet 1 and sheet 2 were renamed....