Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Occurances of a txt string | Excel Discussion (Misc queries) | |||
count occurances | Excel Discussion (Misc queries) | |||
Count consecutive occurances | Excel Worksheet Functions | |||
Need to count occurances in different columns | Excel Worksheet Functions | |||
Count occurances Problem | Excel Worksheet Functions |