Count of different occurances

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
 
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
 
Back
Top