Conditional counting using Offset

[Grendal]

I have a matrix of values that looks like this (simplified)

0,0,0,0,0,5,4,0,7,7
0,0,3,4,7,6,6,6,7,3
0,0,0,0,2,8,3,7,4,5

There are always 10 digits in each row (10 columns) and let's say I want to begin counting from the first nonzero value. I always know the start point for the first nonzero value (I have its position as a separate field), so in the first row I want to count from the 6th digit as it's the first nonzero value for example.

I have a second condition. I want to know (starting from the first nonzero value through to the 10th digit) how many of those values are less than 5, which should give me the following

Row 1 = 2
Row 2 = 3
Row 3 = 3

Anyone have a formula that would get me the preceding? I was trying to use sumproduct with the offset function to know avail. Thanks.

 

[shg]

      A B C D E F G H I J K L M
  1   0 0 0 0 0 5 4 0 7 7   6 2
  2   0 0 3 4 7 6 6 6 7 3   3 3
  3   0 0 0 0 2 8 3 7 4 5   5 3

In M1 and copied down,

=COUNTIFS(INDEX(A1:J1,L1):J1, "<5")

 

[TheCman81]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	0	0	0	0	0	5	4	0	7	7	1
2	0	0	3	4	7	6	6	6	7	3	3
3	0	0	0	0	2	8	3	7	4	5	3
Sheet2

Cell Formulas
Range	Formula
K1	=COUNTIFS(A1:J1,"<5",A1:J1,">0")

Maybe like this?

 

[Aladin Akyurek]

      A B C D E F G H I J K L M
  1   0 0 0 0 0 5 4 0 7 7   6 2
  2   0 0 3 4 7 6 6 6 7 3   3 3
  3   0 0 0 0 2 8 3 7 4 5   5 3

In M1 and copied down,

=COUNTIFS(INDEX(A1:J1,L1):J1, "<5")

And in L1 ?

 

[PCL]

Perhaps
=SUMPRODUCT((A1:J1>0)*(A1:J1<5))

 

[shg]

The OP said it was in another field -- I dunno what he/she used, I used =MATCH(TRUE, A1:J1<>0, 0)

 

[Grendal]

      A B C D E F G H I J K L M
  1   0 0 0 0 0 5 4 0 7 7   6 2
  2   0 0 3 4 7 6 6 6 7 3   3 3
  3   0 0 0 0 2 8 3 7 4 5   5 3

In M1 and copied down,

=COUNTIFS(INDEX(A1:J1,L1):J1, "<5")

Perfect thanks.

Thanks for all the responses guys.

 

[shg]

You're welcome, I'm pleased it helped.