## Excel: List intervals between two values

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

**How to create an array formula**

- Select cell E2
- Type above array formula
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell E2
- Copy (Ctrl + c)
- Select cell range E3:E5
- Paste (Ctrl + v)

**How to remove #num errors**

**Download excel file**

### Category: Count values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]

Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem. […]

Comments(22) Filed in category: Count values, Dates, Excel

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]

Comments(19) Filed in category: Count values, Excel

### Category: Range

How to filter numbers inside ranges in column E and F Array formula in A2: =SMALL(IF(FREQUENCY(IF((COLUMN($A1:$U1)<=End)*(COLUMN($A1:$U1)>=Start), COLUMN($A1:$U1), ""), COLUMN($A1:$U1))>0, ROW($1:$21), […]

Comments(1) Filed in category: Excel, Range, Sort values

### 16 Responses to “Excel: List intervals between two values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

A more condensed version

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

Hi i am trying to do the same thing but with cells that are color formatted. I would like it to be in vba....any idea?

Sam,

Thanks!!

My intention was to create an array from 1 to n. n is the total number of cells in the cell reference.

MATCH(ROW(cell_ref), ROW(cell_ref))

I'm trying to return a value of an adjacent cell when a certain condition is met. Such as if a number is less than or greater than another value in another cell and return the value of the cell next to it.

Jesse,

Download excel *.xlsx file

Jesse.xlsx

Hi Oscar,

I have a list of numbers, let's say: 15,10,5,0, and I have a single value, let's say 6. I want to colour the interval where this single value falls. I used the frequency function to indicate the interval where it falls and then will apply conditional fomatting. I was wondering if there is a more elegant and simple way around it?

Thank you,

Aleksandra

Hello Oscar

I'm working with analysis of some data and wanted a formula that could help me with that.

the data i'm working generates points in a score depending on the position of the value in an amount of intervals.

what I need is that numbers between the intervals below return the value that is in front of them:

0 - 0.25 >>>>>>> 350

0.26 - 0.50 >>>> 300

0.51 - 0.75 >>>> 250

0.76 - 1.00 >>>> 200

1.01 - 1.25 >>>> 150

1.26 - 1.50 >>>> 100

1.51 - 1.75 >>>> 50

more then 1.76 > 0

So, if in the amount of data I have, one of them were 0.88, the value the formula would return me would be 200, and so on. There is such a formula that could help me in doing that automatically?

Just for saying, I'm a foreigner and don't write properly in English, I hope I could make myself clear.

Adriel,

Read this post:

https://www.get-digital-help.com/2010/01/13/return-value-if-in-range-in-excel/

Thank you very much Oscar

it will help me a lot and save me some time =)

I'm using MS Access 2007 and I cannot get this formula to work

can you please explain the formula

In the initial example of finding the interval of a number within a column of numbers, is it possible to find the interval of rows where a given number may exist in one of several columns?

Hey Oscar,

Is there a way to make the range dynamic? For example, instead of $A$1:$A$14 is there a way to use something like "$A$1:$A"&Counta(A:A)? Thanks.

Hi Michael

Yes, there is: https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/

I could not get this formula to work by substituting in a named range for the range a1:a14. How would the formula look using a named range?

=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