Count of different occurances

[Guest]

Hi,
I've been looking to see if anyone else had a similiar query but can't find
it on this excellent resource so hoping someone can help please.

I have got two variables and am trying to count the number of different
occurances within each. To demonstrate I have detailed an example below:

Company Jobe Code
ASB AD200
ASB AD200
MHB AD100
MHB AD200
SQ AD200
etc. (to 10,000 lines of data)

There are several other columns.
On a seperate tab on the same spreadsheet I have listed all the job codes

AD100
AD200
etc

I know need to count the number of companies that have that particular role,
so for the above example, for job code AD200, the answer I'd like is 3 NOT 4
which is the number of time it appears. I would normally do a pivot table but
need this to fit into a table of numbers which will be linked to a mail merge
so want to keep it streamlined. Also does it matter that the Company appears
before the job code?

Hope that makes sense

Appreciate your help.
Thanks

 

[Guest]

Hello Bee,

If your data is in columns A and B of sheet1 and you have your job codes
listed in sheet2 in C2 downwards then in sheet2!D2 use this formula,
confirmed with CTRL+SHIFT+ENTER and copied down

=SUM(IF(FREQUENCY(IF(Sheet1!B$2:B$10000=C2,MATCH(Sheet1!A$2:A$10000,Sheet1!A$2:A$10000,0)),ROW(Sheet1!A$2:A$10000)-ROW(Sheet1!A$2)+1)>0,1))

 

[Guest]

Thank you, that works! You've saved me so much time!
Regards
B

 

[Guest]

Hello again, Is there anyway of making this less system intensive. Since I
added in the formulae for my 280 job codes, it is making my machine freeze.
When I check task manager it tells me that Excel is not responding and so I
have to shut it down.
Thank you