Counting fields on criteria in other fields
 

Hello,

I've got data about different retail branches.

Amongst other fields, I have a column called State which says which
state the branch is in.

There's another column called Special Branch which is a Yes/No column
that specifies if the branch is a specially designed branch.

How do I count the special branches in each state? I used a CountIF
function on the Special Branch column to count how many altogether
across the country. Is there a function to count based on the yes in
the Special Branch column and the state in the State column?


Regards
Stefano

Counting fields on criteria in other fields
 

Hi


With sate in column A and Special in column B
Try
=SUMPRODUCT(--($A$1:$A$1000=StateID),--($B$1;$B$100="y"))

Change ranges to suit.
--
Regards
Roger Govier



wrote in message
ups.com...
Hello,

I've got data about different retail branches.

Amongst other fields, I have a column called State which says which
state the branch is in.

There's another column called Special Branch which is a Yes/No column
that specifies if the branch is a specially designed branch.

How do I count the special branches in each state? I used a CountIF
function on the Special Branch column to count how many altogether
across the country. Is there a function to count based on the yes in
the Special Branch column and the state in the State column?


Regards
Stefano