Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT occurances if criteria is met
I have been up and down trying to find a answer but no luck.
I have a spreadsheet that contains all the employees for the company. One column lists the branch where the employee sits, and another lists the market group they belong to. I need a formula that counts how many locations are in that market group. Any ideas? Using Excel 2003. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT occurances if criteria is met
So if the market for instance would be East and there would be 10 entries
for that market of which 6 are unique you want to return 6? =SUMPRODUCT(--(A2:A400<""),--(A2:A400="East")/COUNTIF(B2:B400,B2:B400&"")) If you want to count it as 10 use =COUNTIF(A2:A400,"East") -- Regards, Peo Sjoblom "John" wrote in message ... I have been up and down trying to find a answer but no luck. I have a spreadsheet that contains all the employees for the company. One column lists the branch where the employee sits, and another lists the market group they belong to. I need a formula that counts how many locations are in that market group. Any ideas? Using Excel 2003. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT occurances if criteria is met
Peo...You are correct in what I am looking to return, but the formula does
not return the correct number. I get an answer that is not a whole#. Any ideas? "Peo Sjoblom" wrote: So if the market for instance would be East and there would be 10 entries for that market of which 6 are unique you want to return 6? =SUMPRODUCT(--(A2:A400<""),--(A2:A400="East")/COUNTIF(B2:B400,B2:B400&"")) If you want to count it as 10 use =COUNTIF(A2:A400,"East") -- Regards, Peo Sjoblom "John" wrote in message ... I have been up and down trying to find a answer but no luck. I have a spreadsheet that contains all the employees for the company. One column lists the branch where the employee sits, and another lists the market group they belong to. I need a formula that counts how many locations are in that market group. Any ideas? Using Excel 2003. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT occurances if criteria is met
Try this
=SUMPRODUCT(--(A2:A400<""),--(A2:A400="East"),--(B2:B400<"")/COUNTIF(B2:B400,B2:B400&"")) -- Regards, Peo Sjoblom "John" wrote in message ... Peo...You are correct in what I am looking to return, but the formula does not return the correct number. I get an answer that is not a whole#. Any ideas? "Peo Sjoblom" wrote: So if the market for instance would be East and there would be 10 entries for that market of which 6 are unique you want to return 6? =SUMPRODUCT(--(A2:A400<""),--(A2:A400="East")/COUNTIF(B2:B400,B2:B400&"")) If you want to count it as 10 use =COUNTIF(A2:A400,"East") -- Regards, Peo Sjoblom "John" wrote in message ... I have been up and down trying to find a answer but no luck. I have a spreadsheet that contains all the employees for the company. One column lists the branch where the employee sits, and another lists the market group they belong to. I need a formula that counts how many locations are in that market group. Any ideas? Using Excel 2003. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT occurances if criteria is met
Hi,
Assuming your data is in range A1:B6 as follows: Branch Group Delhi Marketing Mumbai Finance Calcutta Operations Mumbai Marketing Mumbai Operations Enter the Marketing in cell A8 and enter the following array formula (confirmed by Ctrl+Shift+Enter) in cell B8. =ROUNDUP(SUM(IF(($B$1:$B$6=$A8),1/COUNTIF($A$1:$A$6,A1:A6),0)),0). Hope this helps. -- Regards, Ashish Mathur www.ashishmathur.com http://www.linkedin.com/in/excelenthusiasts "John" wrote in message ... I have been up and down trying to find a answer but no luck. I have a spreadsheet that contains all the employees for the company. One column lists the branch where the employee sits, and another lists the market group they belong to. I need a formula that counts how many locations are in that market group. Any ideas? Using Excel 2003. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurances | Excel Worksheet Functions | |||
Count of different occurances | Excel Discussion (Misc queries) | |||
Count Occurances of a txt string | Excel Discussion (Misc queries) | |||
count occurances | Excel Discussion (Misc queries) | |||
count occurances in a row using multiple criteria | Excel Worksheet Functions |