David asks:

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.

Calculate the number of contiguous occurrences of a value

Array formula in cell B3:

Read Rick Rothsteins's comment.

=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.

Download excel *.xlsx file

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