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.

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.

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.

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.

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.