jeyner asks:

I need to count in a list the interval between the same value.

Example list,

1-2-3-1-4-5-1-6-7-8-9-7-8-1

So the answer must be for the value 1 the spaces are 2,2,6.

thank you

Answer:

Array Formula in cell E2:

=SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1)+1)-SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1))-1

How to create an array formula

  1. Select cell E2
  2. Type above array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

How to copy array formula

  1. Select cell E2
  2. Copy (Ctrl + c)
  3. Select cell range E3:E5
  4. Paste (Ctrl + v)

How to remove #num errors

=IFERROR(SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1)+1)-SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1))-1,"")

Download excel file

List interval between two values.xls