Count occurances of multiple values

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/as...561181033.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

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

>-----Original Message-----
>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
>.
>

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


"Biff" wrote:

> 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
>
> >-----Original Message-----
> >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
> >.
> >
>