## Count cells between a given value

*Article last updated on April 27, 2018*

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

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

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

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

How to filter numbers inside (and outside) number ranges in excel

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), […]

### 16 Responses to “Count cells between a given value”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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