Counting fields on criteria in other fields

[buattis]

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

 

[Roger Govier]

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.