Often you may want to find the first 3 positive numbers in a column in Excel.
For example, you might have a column of values in Excel and you’d like to return the first 3 positive numbers:
The following example shows how to do.
Example: Find First 3 Positive Numbers in Column in Excel
Suppose we have the following list of values in Excel:
Notice that some values are positive, some are negative, and some are zero.
To find the first 3 positive values in the column, we can first list out the numbers 1 through 3 in column C:
Next, we can type the following formula into cell D1 to find the first positive value in column A:
=INDEX($A$2:$A$14,SMALL(IF($A$2:$A$14>0,ROW($A$2:$A$14)-ROW($A$2)+1),C1))
We can then click and drag this formula down to cells D2 and D3 to find the second and third positive values in column A, respectively:
Column D now displays the first 3 positive values from column A: 9, 12, and 15.
Note that you can use this formula to find the first n positive values in a column.
For example, we could easily drag our formula down to more cells to list out the first 5 positive values from column A:
Column D now displays the first 5 positive values from column A.
Feel free to use this formula to list out the first n positive values from your own dataset.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Find First Negative Value in Range
Excel: How to Use IF Function with Negative Numbers
Excel: How to Find Last Value in Column Greater than Zero
