Count of different occurances
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
"daddylonglegs" wrote:
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(S heet1!A$2:A$10000,Sheet1!A$2:A$10000,0)),ROW(Sheet 1!A$2:A$10000)-ROW(Sheet1!A$2)+1)0,1))
"Bee" wrote:
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
|