# counting non-blanks

#### skhou

##### Board Regular
Hi guys,

I've got the following situation, which I'm wondering whether I can solve using a formula, rather then coding it.

I have a the following list of values:

0,0,0,3,6,0,8,0,0,1

What I want to do is be able to choose the value in the lost that I want to pull out which is not Zero. e.g Is there a way of being able to pull out say the first value which is not zero being a "3" or the 2nd value in the list which is not zero being a "6", or the third value in the list which is not zero being a "8"?

thanks,

Sonny

### 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.
Let A2:A11 house the data and C2 a value like 2, meaning get the 2nd greater than 0 value.

Control+shift+enter, not just enter...

=INDEX(\$A\$2:\$A\$11,SMALL(IF(ISNUMBER(\$A\$2:\$A\$11),IF(\$A\$2:\$A\$11>0,ROW(\$A\$2:\$A\$11)-ROW(\$A\$2)+1)),C2))

Aladin - That works. Thanks heaps !

Aladin - Just for my own understanding, are you able to explain the logic in this formula?

thanks

Aladin - Just for my own understanding, are you able to explain the logic in this formula?

thanks

1) Lets p stand for the position at which the Nth greater than 0 numeric value is located, thus:

INDEX(\$A\$2:\$A\$11, p)

If we can calculate p, INDEX will return the value at pth cell in A2:A11.

2) First we can calculate the set of greater than 0 numeric values with:

IF(ISNUMBER(\$A\$2:\$A\$11),IF(\$A\$2:\$A\$11 > 0, x))

2a) If we equate x to \$A\$2:\$A\$11, we would get something like

{FALSE;FALSE;FALSE;3;6;FALSE;8;FALSE;FALSE;1}

where FALSE corresponds to cells that are either empty or housing numeric values less than or equal to 0.

2b) If we equate x to ROW(\$A\$2:\$A\$11)-ROW(\$A\$2)+1, we would get something like

{FALSE;FALSE;FALSE;4;5;FALSE;7;FALSE;FALSE;10}

where the integers are the positions which correspond to cells housing greater than 0 numeric values. What FALSE means is easy to derive by now.

Note that

ROW(\$A\$2:\$A\$11)-ROW(\$A\$2)+1

is just

{1;2;3;4;5;6;7;8;9;10}

which is a sequential numbering of the celss of A2:A11.

3) If we apply SMALL to x which holds positions like in

SMALL(x,N) == p

where N is given in C2, we are giving INDEX what it needs to return the desired result.

Aladin - Understood. Thankyou for the very thorough explanation. Fantastic Stuff!

cheers,

Skhou

Replies
1
Views
408
Replies
4
Views
634
Replies
1
Views
207
Replies
9
Views
272
Replies
3
Views
253

1,196,103
Messages
6,013,482
Members
441,767
Latest member
Craigh4444

### 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