locating cell info from one sheet

[omss]

Hi,
This problem has been bothering me for a long while
I have a table on one sheet with multiple people names occurring multiple
times and different kinds of info under different columns, but I only want to
locate the negetive values from a certain column and use the count function
or whatever means necessary to have excel return the NUMBER of negetive
values from that one column under one person's name from the table
so for ex:

Sheet1

Name Age NUMBER
Alpha 23 -2
Beta 21 1
Gamma 20 -899
Omega 25 365
Alpha 23 -96
Beta 21 45
Beta 20 -31
Alpha 25 -65


Sheet 2

Name # of negatives from the Number column
Alpha 3
Beta 1
Gamma 1
Omega 0

I want to have the second column of Sheet 2 look like the one above.
ALSO, i have to make it so that i can add more names to the list and be
fine. I can probably create another tab where I can have the names in
alphabetical order, but that didn't make anything better. I would really
appreciate the help.

 

[Sandy Mann]

With the data that you gave in A1:C9 try:

=SUMPRODUCT((A2:A9="Alpha")*(C2:C9<0))

Although it is better to put the name ("Alpha") in a cell (without the
quotes) and use:

=SUMPRODUCT((A2:A9=D1)*(C2:C9<0))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk