# List only cells with positive values

#### tghcogo

##### New Member
Hello all

I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### T. Valko

##### Well-known Member
Hello all

I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?
What version of Excel are you using?

Oops it's 2003

#### T. Valko

##### Well-known Member
Oops it's 2003
OK, since you'll be adding data we need to create a dynamic named range.

Name: Values (or whatever name you want to use as long as it's a valid name!)
Refers to: =\$B\$2:INDEX(\$B\$2:\$B\$100,COUNTA(\$A\$2:\$A\$100))
Use a reasonable end of range that allows for expected future data addition.
OK out

I'm assuming that there are no empty/blanks cells within the data range.

Enter this formula in C1. This will return the count of values that are >0.

=COUNTIF(Values,">0")

Enter this array formula** in C2:

=IF(ROWS(C\$2:C2)>C\$1,"",INDEX(A:A,SMALL(IF(Values>0,ROW(Values)),ROWS(C\$2:C2))))

** 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 start getting blank cells then copy down additional cells to allow for future returns when new data is added to the data table. How many rows should you copy the formula to? I don't know, only you know how many returns are possible.

Last edited:

#### tghcogo

##### New Member
Thanks a bunch, it works, and I' m embedding it into a larger spreadsheet.

#### T. Valko

##### Well-known Member
Thanks a bunch, it works, and I' m embedding it into a larger spreadsheet.
You're welcome. Thanks for the feedback!

#### Chester21

##### New Member
This works very well.... How would I make the corresponding values show in column D?
Column C now shows what values are positive, but could column D show what those values are?

Thanks,
Chester21

Replies
1
Views
61
Replies
1
Views
265
Replies
1
Views
85
Replies
0
Views
77
Replies
3
Views
206

1,191,693
Messages
5,988,131
Members
440,126
Latest member
Dee8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back