Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cene K
 
Posts: n/a
Default 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?
  #2   Report Post  
Gary76
 
Posts: n/a
Default COUNTIFs with multiple criteria

Look at SUMIF

=SUMIF(rangewithnamesmanytimes,cellwithnamein,rang ewithnumbers)

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?

  #3   Report Post  
Cene K
 
Posts: n/a
Default COUNTIFs with multiple criteria

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,rang ewithnumbers)

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?

  #4   Report Post  
RagDyeR
 
Posts: n/a
Default COUNTIFs with multiple criteria

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" wrote in message
...
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,rang ewithnumbers)

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?



  #5   Report Post  
Cene K
 
Posts: n/a
Default COUNTIFs with multiple criteria

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" wrote in message
...
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,rang ewithnumbers)

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?






  #6   Report Post  
RagDyer
 
Posts: n/a
Default COUNTIFs with multiple criteria

Thanks for the feed-back.

--
Regards,

RD

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

"Cene K" wrote in message
...
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" wrote in message
...
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,rang ewithnumbers)

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?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Criteria in SumProduct, N/A Result dcd123 Excel Worksheet Functions 7 October 7th 05 01:26 PM
Counting by multiple criteria Risky Dave Excel Worksheet Functions 4 September 28th 05 01:29 PM
Extract multiple records matching criteria from list William DeLeo Excel Worksheet Functions 12 June 30th 05 02:35 PM
Multiple Criteria IF Nesting BethB Excel Worksheet Functions 2 May 17th 05 12:14 AM
Can I get the mode, min, and max with multiple criteria? BobT Excel Discussion (Misc queries) 1 February 15th 05 04:20 AM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"