Count occurances of multiple values

[BaseballFan]

I am trying to analyze a list of baseball teams and the number of players
they have at each position. For example, I have two columns that I am
working with: column 1 is the team name, and column 2 is the position. So,
my datasheet looks like this:
Team Position
Bears 1B
Bears 1B
Bears 1B
Bears 2B
Bears SS
Lions 1B
Lions Catcher
Lions SS
Lions P

I want to count how many times the string "1B" appears for the team,
"Bears". I'm using the formula example from Office Online
(http://office.microsoft.com/en-us/assistance/HP030561181033.aspx):
=SUM(IF(A2:A9="Bears",IF(B2:B9="1B",1,0)))
But it returns a value of zero... as you can see, it should return a value
of "3". Any thoughts?

Jimmy

 

[Biff]

Hi!

That formula will work but it's an array formula. When you
type it in instead of just hitting ENTER, you have to use
the key combo of CTRL,SHIFT,ENTER.

Use this formula instead, normally entered:

=SUMPRODUCT(--(A2:A9="Bears"),--(B2:B9="1B"))

Or, even better:

In cell C1 enter the team name you're interested in. In
cell D1 enter the position you're interested in:

C1 = Bears
D1 = 1B

=SUMPRODUCT(--(A2:A9=C1),--(B2:B9=D1))

Biff

 

[BaseballFan]

Thanks, Biff. The SUMPRODUCT worked like a champ. I did use the
CTRL+SHIFT+ENTER on my original formula, but got a #num error. I had
forgotten about that when I posted my question. Oh well, I now have what I
need... Thanks, again.

Jimmy