COUNT occurances if criteria is met

[John]

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.

 

[Peo Sjoblom]

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]

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]

Try this

=SUMPRODUCT(--(A2:A400<>""),--(A2:A400="East"),--(B2:B400<>"")/COUNTIF(B2:B400,B2:B400&""))



--


Regards,


Peo Sjoblom

 

[Ashish Mathur]

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