Conditional counting using Offset

Grendal

New Member
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.

Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Code:
``````      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")

Excel 2010
ABCDEFGHIJK
100000540771
200347666733
300002837453
Sheet2
Cell Formulas
RangeFormula
K1=COUNTIFS(A1:J1,"<5",A1:J1,">0")

Maybe like this?

Code:
``````      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 ?

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

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

Code:
``````      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.

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

Replies
8
Views
118
Replies
3
Views
236
Replies
7
Views
385
Replies
16
Views
320
Replies
2
Views
133

1,203,347
Messages
6,054,878
Members
444,760
Latest member
TeckTeck

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.

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