Did you remember the Control Shift Enter?
--
David Biddulph

"Eric D" wrote in message
...
ok thank you so much ... i tried these formula's and they seem to work,
but
the long formula returns a #VALUE but when i click on the "fx" beside the
formula bar to look at it, it says its returning a value that would be
correct but on the spreadsheet it is saying #VALUE. did i do something
wrong?
--
Eric


"T. Valko" wrote:

Assuming every name is unique and there are no empty cells in the $$
column.

Assuming your data is on Sheet1A2:B300

In the formula...

Name: refers to Sheet1!A$2:A$300
Amount: refers to Sheet1!B$2:B$300

On Sheet2 enter this formula in A1:

=COUNTIF(Amount,"<0")

This will return the number of records that meet the criteria.

Enter this array formula** in A2 to get the names:

=IF(ROWS(A$2:A2)A$1,"",INDEX(Name,SMALL(IF(Amount <0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down until you get blanks. If you will need to do this often and the
number of records will vary then you'll have to copy the formula to a
number
of cells that you know will be enough to get all the results. You're the
only one that knows how many that will be!

Enter this formula in B2 to get the amounts:

=IF(A2="","",SUMIF(Name,A2,Amount))

Copy down to the same number of cells as the formula in column A.

--
Biff
Microsoft Excel MVP


"Eric D" wrote in message
...
there are 300+ rows and the numbers are not always positive
--
Eric


"T. Valko" wrote:

How many rows of data is there on Sheet1?

The numbers in column B, are they always positive numbers?

--
Biff
Microsoft Excel MVP


"Eric D" wrote in message
...
is it possible to have an if statement or something that could
return
true
values in a list? ex. on a separate tab i have a sheet that has 2
columns
in
column 1 is names and in column 2 is $$. Is there a formula i can
enter
on a
separate sheet so that it gives the answer but without spaces.

spreadsheet 1 spreadsheet 2
desired result
col1 col2 col1 col2
eric 20 eric 20
dave 0 clark 10
clark 10 mike 5
lori 0
stan 0
mike 5

any thoughts?
--
Eric