Count of different occurances
 

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

Count of different occurances
 

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

Count of different occurances
 

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

"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

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