COUNTIFs with multiple criteria

C

Cene K

I have a column with a list of names and a table with those same names
repeated many times with a value from one to five in the column next to them.
I need a formula that will first determine if a name in my list matches the
name in the table, and then tally the number of times the value "1" occurs.

It seems like I should be using a COUNTIF and some sort of lookup table, but
I can't seem to get it right. Can anyone help?
 
C

Cene K

This is closer, but still not what I need. Let me show you what I mean.

I have "Name1" in A5, and my table in columns R and S. It looks something
like this:

Name1 3
Name1 5
Name1 3
Name1 4
Name1 1
Name1 1
Name2 4
Name2 3
Name2 4
Name2 1
Name3 2
. . . and so on

=SUMIF(R:R,A5,S:S) returns 18 because it is summing all the values where
Name1 is in column R. I need something that will give me a count of the
number of times "1" appears, so my result should be 2. Preferably something
that I can also use to tally the number of times 2, 3, 4, and 5 appear as
well.
 
R

RagDyeR

Try this:

=SUMPRODUCT((R1:R100=A5)*(S1:S100=1))


You could also use a cell reference to contain the number you're looking
for, just as you used A5 to contain the name.

=SUMPRODUCT((R1:R100=A5)*(S1:S100=A6))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

This is closer, but still not what I need. Let me show you what I mean.

I have "Name1" in A5, and my table in columns R and S. It looks something
like this:

Name1 3
Name1 5
Name1 3
Name1 4
Name1 1
Name1 1
Name2 4
Name2 3
Name2 4
Name2 1
Name3 2
. . . and so on

=SUMIF(R:R,A5,S:S) returns 18 because it is summing all the values where
Name1 is in column R. I need something that will give me a count of the
number of times "1" appears, so my result should be 2. Preferably something
that I can also use to tally the number of times 2, 3, 4, and 5 appear as
well.
 
Top