COUNTIFs with multiple criteria

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?

Look at SUMIF

=SUMIF(rangewithnamesmanytimes,cellwithnamein,rangewithnumbers)

HTH

"Cene K" wrote:

> 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?

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.

"Gary76" wrote:

> Look at SUMIF
>
> =SUMIF(rangewithnamesmanytimes,cellwithnamein,rangewithnumbers)
>
> HTH
>
> "Cene K" wrote:
>
> > 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?

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!
=====================================================

"Cene K" <[email protected]> wrote in message
news:[email protected]...
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.

"Gary76" wrote:

> Look at SUMIF
>
> =SUMIF(rangewithnamesmanytimes,cellwithnamein,rangewithnumbers)
>
> HTH
>
> "Cene K" wrote:
>
> > 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?

That is exactly what I was looking for. Thank you so much!

"RagDyeR" wrote:

> 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!
> =====================================================
>
> "Cene K" <[email protected]> wrote in message
> news:[email protected]...
> 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.
>
> "Gary76" wrote:
>
> > Look at SUMIF
> >
> > =SUMIF(rangewithnamesmanytimes,cellwithnamein,rangewithnumbers)
> >
> > HTH
> >
> > "Cene K" wrote:
> >
> > > 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?
>
>
>

Thanks for the feed-back.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Cene K" <[email protected]> wrote in message
news:[email protected]...
> That is exactly what I was looking for. Thank you so much!
>
> "RagDyeR" wrote:
>
> > 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!
> > =====================================================
> >
> > "Cene K" <[email protected]> wrote in message
> > news:[email protected]...
> > 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.
> >
> > "Gary76" wrote:
> >
> > > Look at SUMIF
> > >
> > > =SUMIF(rangewithnamesmanytimes,cellwithnamein,rangewithnumbers)
> > >
> > > HTH
> > >
> > > "Cene K" wrote:
> > >
> > > > 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?
> >
> >
> >