counting totals based on certain criteria - Excel 2K

[tech1NJ]

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

 

[Mike H]

Hi,

Put this in sheet 2 B3 and drag down as required

=COUNTIF(Sheet1!$C$1:$C$500,A3)

Mike

 

[John C]

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]

Ooops, mine should have been A3 in the formula

 

[tech1NJ]

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.

 

[John C]

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.

 

[tech1NJ]

This did it. Thanks. By the way, what is the function of the 2 dashes before
the ISNUMBER?

 

[John C]

It is double unary, if you search on that, you can find some detailed
explanations.

 

[tech1NJ]

Got it. Thanks
--
tech1NJ

It is double unary, if you search on that, you can find some detailed
explanations.

 

[tech1NJ]

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