Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s
of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
Hi,
Put this in sheet 2 B3 and drag down as required =COUNTIF(Sheet1!$C$1:$C$500,A3) Mike "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
On sheet 2, in cell B3:
=COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
Ooops, mine should have been A3 in the formula :)
-- ** John C ** "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
This returns the value of 0. I'm not sure why. Here is a bit more of relavant
info. Sheet 1 Column C has info with the name found on Sheet 2 Column A and it has other information on it as well. The name Djoe may appear more than once for different reasons. I like to count those cells that contains his name and place that count next to his name on Sheet2 A2. The same for JMaine. I am including an example of what may be found in Sheet 1 Column C1. Example: Djoe Authorized to purchase system licenses. for 2 years. JMaine has accessed the database. Djoe last car scan was 11/18/07. JMaine logged out of network last 12/7/07. -- tech1NJ "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
Okay. Yes, mentioning that there is more than just the name in Sheet1 cells
that does explain this. Try this one on for size: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$C$2:$C$1000))) Hope this helps. -- ** John C ** "tech1NJ" wrote: This returns the value of 0. I'm not sure why. Here is a bit more of relavant info. Sheet 1 Column C has info with the name found on Sheet 2 Column A and it has other information on it as well. The name Djoe may appear more than once for different reasons. I like to count those cells that contains his name and place that count next to his name on Sheet2 A2. The same for JMaine. I am including an example of what may be found in Sheet 1 Column C1. Example: Djoe Authorized to purchase system licenses. for 2 years. JMaine has accessed the database. Djoe last car scan was 11/18/07. JMaine logged out of network last 12/7/07. -- tech1NJ "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
This did it. Thanks. By the way, what is the function of the 2 dashes before
the ISNUMBER? -- tech1NJ "John C" wrote: Okay. Yes, mentioning that there is more than just the name in Sheet1 cells that does explain this. Try this one on for size: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$C$2:$C$1000))) Hope this helps. -- ** John C ** "tech1NJ" wrote: This returns the value of 0. I'm not sure why. Here is a bit more of relavant info. Sheet 1 Column C has info with the name found on Sheet 2 Column A and it has other information on it as well. The name Djoe may appear more than once for different reasons. I like to count those cells that contains his name and place that count next to his name on Sheet2 A2. The same for JMaine. I am including an example of what may be found in Sheet 1 Column C1. Example: Djoe Authorized to purchase system licenses. for 2 years. JMaine has accessed the database. Djoe last car scan was 11/18/07. JMaine logged out of network last 12/7/07. -- tech1NJ "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
It is double unary, if you search on that, you can find some detailed
explanations. -- ** John C ** "tech1NJ" wrote: This did it. Thanks. By the way, what is the function of the 2 dashes before the ISNUMBER? -- tech1NJ "John C" wrote: Okay. Yes, mentioning that there is more than just the name in Sheet1 cells that does explain this. Try this one on for size: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$C$2:$C$1000))) Hope this helps. -- ** John C ** "tech1NJ" wrote: This returns the value of 0. I'm not sure why. Here is a bit more of relavant info. Sheet 1 Column C has info with the name found on Sheet 2 Column A and it has other information on it as well. The name Djoe may appear more than once for different reasons. I like to count those cells that contains his name and place that count next to his name on Sheet2 A2. The same for JMaine. I am including an example of what may be found in Sheet 1 Column C1. Example: Djoe Authorized to purchase system licenses. for 2 years. JMaine has accessed the database. Djoe last car scan was 11/18/07. JMaine logged out of network last 12/7/07. -- tech1NJ "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
Got it. Thanks
-- tech1NJ "John C" wrote: It is double unary, if you search on that, you can find some detailed explanations. -- ** John C ** "tech1NJ" wrote: This did it. Thanks. By the way, what is the function of the 2 dashes before the ISNUMBER? -- tech1NJ "John C" wrote: Okay. Yes, mentioning that there is more than just the name in Sheet1 cells that does explain this. Try this one on for size: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$C$2:$C$1000))) Hope this helps. -- ** John C ** "tech1NJ" wrote: This returns the value of 0. I'm not sure why. Here is a bit more of relavant info. Sheet 1 Column C has info with the name found on Sheet 2 Column A and it has other information on it as well. The name Djoe may appear more than once for different reasons. I like to count those cells that contains his name and place that count next to his name on Sheet2 A2. The same for JMaine. I am including an example of what may be found in Sheet 1 Column C1. Example: Djoe Authorized to purchase system licenses. for 2 years. JMaine has accessed the database. Djoe last car scan was 11/18/07. JMaine logged out of network last 12/7/07. -- tech1NJ "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting totals based on certain criteria - Excel 2K
Hi John, I have one more function I need your help with.
The same sheets 1 and 2 are in play for this application. This time I added column C to sheet 2. I like to know when the last time the person in Sheet 2 column A was found in the list under Sheet 1 column C. If not found, then Sheet 2 Column C should indicate "Not Found". Sheet 1 Column A has dates (formated DD/MM/YY) and column B has time (formated hh:mm AM). The additonal item that I need to now about is on Sheet 1 Column A (Dates) and Column B (time). . I appreciate your continued help on this -- tech1NJ "John C" wrote: It is double unary, if you search on that, you can find some detailed explanations. -- ** John C ** "tech1NJ" wrote: This did it. Thanks. By the way, what is the function of the 2 dashes before the ISNUMBER? -- tech1NJ "John C" wrote: Okay. Yes, mentioning that there is more than just the name in Sheet1 cells that does explain this. Try this one on for size: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$C$2:$C$1000))) Hope this helps. -- ** John C ** "tech1NJ" wrote: This returns the value of 0. I'm not sure why. Here is a bit more of relavant info. Sheet 1 Column C has info with the name found on Sheet 2 Column A and it has other information on it as well. The name Djoe may appear more than once for different reasons. I like to count those cells that contains his name and place that count next to his name on Sheet2 A2. The same for JMaine. I am including an example of what may be found in Sheet 1 Column C1. Example: Djoe Authorized to purchase system licenses. for 2 years. JMaine has accessed the database. Djoe last car scan was 11/18/07. JMaine logged out of network last 12/7/07. -- tech1NJ "John C" wrote: On sheet 2, in cell B3: =COUNTIF(Sheet1!$C$2:$C$1000,A2) Note: the range should include all the data entries from Sheet1 Then copy this formula all the way down to B18. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "tech1NJ" wrote: I am working with a book and 2 sheets. Sheet 1 has data on 3 columns and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18 that may show up on Sheet 1 column C. What I like to do is count the number of times the name appears in Sheet 1 Column C. The number should be logged into Sheet 2 B3 to B18. Is there a way to do this. PLEASE HELP!!!!!!!!!!!!!!!-- tech1NJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add totals based on more than one criteria | Excel Worksheet Functions | |||
Summing totals on separate worksheet based on 2 criteria | Excel Discussion (Misc queries) | |||
Counting based on criteria from two cells??? | Excel Worksheet Functions | |||
Totals based on meeting multiple criteria | Excel Worksheet Functions | |||
counting based on criteria | Excel Worksheet Functions |