# Office 365 >  >  Count number of cells between first and last non-zero value in row

## sukhiiii

Hi
I want to count the number of cells between the first and last non-zero value in a row.
Ex: Below row of numbers should return 6 (6 cells from the first non-zero value i.e. 4 to the last one i.e. 3).

0	0	4	5	0	0	2	3	0	0	0

Can someone help with the right function for this?

----------


## AliGW

Welcome to the forum!

Try this:

*Excel 2016 (Windows) 32 bit*

A
B
C
D
E
F
G
H
I
J
K

*1*
0
0
4
5
0
0
2
3
0
0
0

*2*












*3*

6











Sheet: *Sheet1*



*Excel 2016 (Windows) 32 bit*

B

*3*
=(LOOKUP(2,1/(1:1>0),COLUMN(1:1))+1)-(MATCH(1,1:1,1)+1)


Sheet: *Sheet1*

----------


## sukhiiii

Thanks! Could you pls explain how that worked?

----------


## XOR LX

Hi,

=MMULT(AGGREGATE({14,15},6,COLUMN(A1:K1)/A1:K1^0,1),{1;-1})+1

Regards

----------


## XOR LX

> Welcome to the forum!
> 
> Try this:
> 
> *Excel 2016 (Windows) 32 bit*
> 
> A
> B
> C
> ...



What happens if, for example, the first and last entries are non-zero and all the rest zero?

Regards

----------


## XOR LX

Hi Ali,

Just noticed why your formula won't always give correct results. With a _match_type_ parameter of 1, the _lookup_array_ must be *in ascending order*.

Regards

----------


## AliGW

It won't matter in this case (I did think about it!) - we are looking for the first number larger than or equal to 1. 

*Excel 2016 (Windows) 32 bit*

A
B
C
D
E
F
G
H
I
J
K

*1*
0
0
22
3
0
0
2
3
0
0
0

*2*












*3*

6











Sheet: *Sheet1*



As for your query in post #5: I can't imagine a scenario like that given the information given. Anyway, the OP seems happy with it.

----------


## AliGW

If that takes care of your original question, please select _Thread Tools_ from the menu link above and mark this thread as SOLVED. Thanks.

----------


## XOR LX

> It won't matter in this case (I did think about it!)



Sorry, what won't matter? You mean the fact that the values in the range aren't in ascending order won't affect your choice of MATCH with a _match_type_ parameter of 1?

I hope not, since that is an absolute requisite for that function with that parameter!

Regards

----------


## AliGW

What I mean is that it does not matter what the first number larger than 0 is or whether it is in ascending order in the range that follows - it's only that first number that matters in this case and the formula returns the correct column regardless of what that number is. It can be bigger than, the same as or smaller than the second number bigger than 0 - the formula will still work. In other words, the MATCH is only going to return the column number of the first match. Perhaps you could show me in what way you think it fails?

----------


## XOR LX

0
*2*
1
9
7
1
2
0
3
*2*
0



The first and last non-zeroes in the above are highlighted. The result should be 9, yet your formula returns 2.

Regards

----------


## XOR LX

> Hi,
> 
> =MMULT(AGGREGATE({14,15},6,COLUMN(A1:K1)/A1:K1^0,1),{1;-1})+1
> 
> Regards



Apologies. That should've been:

=1+MMULT(AGGREGATE({14,15},6,COLUMN(A1:K1)/A1:K1^0,1),{1;-1})

Regards

----------


## AliGW

Ah, I see what you mean. Back to the drawing board!

----------


## XOR LX

Actually we can combine two MATCH statements as well, so this works for pre-Excel 2010:

=1+MMULT(MATCH({1,0},0/1:1,{1,0}),{1;-1})

Regards

----------


## AliGW

That's a nice compromise!  :Smilie:

----------


## XOR LX

> That's a nice compromise!



Compromises are good!  :Smilie:

----------

