Count number of cells between first and last non-zero value in row

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

Hi,

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

Regards

Originally Posted by 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

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

Regards

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

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.

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

Originally Posted by AliGW

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

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?

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

Originally Posted by 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

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

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

That's a nice compromise!

Originally Posted by AliGW

That's a nice compromise!

Compromises are good!