Author: Oscar Cronquist Article last updated on April 23, 2017

Hi Oscar,
In column A, I have a long random list of two variables, "N/A" and the value 1. In column B I want to identify the number of contiguous occurrences of the value 1 before the next appearance of "N/A". I wonder if you could point me to the best way of achieving this please? Example below shows what I am after.Much appreciation for your excellent website,
DavidCol A Col B
N/A
N/A
1
1
1
1
1 5
N/A
1
1
1
1 4
N/A
N/A
1
1 2
N/A
N/A
N/A
1 1

Thanks, David.

The following formula looks for contiguous values, not only 1 and N/A.

Array formula in cell B3:

=IF(SUM(IF(\$A\$2:A3<>\$A\$3:A4, 1, 0))=SUM(IF(\$A\$2:A2<>\$A\$3:A3, 1, 0)), "", INDEX(FREQUENCY(IF(\$A\$2:\$A\$25<>\$A\$3:\$A\$26, "", MATCH(ROW(\$A\$2:\$A\$25), ROW(\$A\$2:\$A\$25))), IF(\$A\$2:\$A\$25<>\$A\$3:\$A\$25, MATCH(ROW(\$A\$2:\$A\$25), ROW(\$A\$2:\$A\$25)), ""))+1, SUM(IF(\$A\$2:A3<>\$A\$3:A4, 1, 0))))

How to enter an array formula

1. Select cell B3
2. Paste above formula to formula bar
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter once
5. Release all keys

Copy cell B3 and paste to cells below as far as needed.

If you want to learn more about array formulas join my Advanced excel course.