Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |