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

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:**

Read Rick Rothsteins’s comment.

**How to enter an array formula**

- Select cell B3
- Paste above formula to formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- 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.

### Download excel *.xlsx file

identify-the-number-of-contiguous-occurrences-of-a-value-david.xlsx

First off, you show your formula starting in cell B3… you need to adjust it so that it starts in cell B2, otherwise you will not show a 1 in cell B2 if cell A2 differs from cell A3.

Second, here is a much simpler, still array-entered, formula, placed in cell B2 and copied down, that appears to produce the same output as your formula…

=IF(A3<>A4,ROW()-MAX(IF(A$1:A2<>A3,ROW(A$1:A2))),””)

Reply

Sorry, I posted the wrong formula, plus it looks like the comment processor ate my less than greater than symbol. Here is the correct array-entered formula, rearrange to eliminate the display problem…

=IF(A2=A3,””,ROW()-MAX(IF(A$1:A1<>A2,ROW(A$1:A1))))

Reply

D@MN! I missed that the comment processor ate a second less than, greater than symbols in my formula. Here now is the correct [b]array-entered[/b] formula (rearranged so that there are no less than, greater than symbols at all)..

=IF(A2=A3,””,ROW()-MAX(IF(A$1:A1=A2,,ROW(A$1:A1))))

Reply

Rick Rothstein (MVP – Excel),

Your formulas work fine, thanks. So much better and smaller than mine.

I am sorry for wordpress eating your less/greater than signs, I try to edit your comments as soon as I can.

Reply