## Excel formula to count cells between two values

*Article updated on November 15, 2013*

### Count cells between two values in a column

**Formula in D15:**

### Count cells between two values in a row

Formula in D23:

### Download excel sample file for this tutorial.

formula to count cells between two values.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**ABS(**number**)**

Returns the absolute value of a number, a number without its sign.

Count how many numbers between a specific value occurring multiple times

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 […]### 4 Responses to “Excel formula to count cells between two values”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

How about this array formula?

=ROUND(SUM(MATCH(C12:C13,A1:A10,0)/2),0)

Thank you so much! I've been looking for this everywhere!!

Abby,

Thank you! Remember that the function only works if there are unique values (no duplicates) in the cell range (A1:A10).

hello! thank you so much! this is what i was looking for, but i need some more help if you can. My row data have duplicates, so its not working properly all the time, although every time i know a value that is between those two. Can this formula be adjusted to lookup a value between those two, so it will work?

thanks you again for your help!

Happy new year!