The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 and H is in B9, cells B4, B5, B6, B7 and B8 are in between.

Explaining formula in cell E16

Step 1 - Find value in cell D13 in cell range B2:B11

The MATCH function finds the relative position of a value in an array or cell range.

MATCH(D13, B2:B11, 0)

becomes

MATCH("B", {"A"; "B"; "V"; "X"; "C"; "T"; "N"; "H"; "A"; "C"}, 0)

and returns 2.

Step 2 - Find value in cell D14 in cell range B2:B11

MATCH(D14, B2:B11, 0)

becomes

MATCH("H", {"A"; "B"; "V"; "X"; "C"; "T"; "N"; "H"; "A"; "C"}, 0)

and returns 8.

Step 3 - Subtract positions

MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0)

becomes

2-8 equals -6.

Step 4 - Remove sign

We don't know where the values are in the cell range so it may happen that we get a negative number from time to time, this example is such occasion. The ABS function removes the sign from a number.

ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))

becomes

ABS(-6)

and returns 6.

Step 5 - Subtract with 1

The calculation counts the last cell as well, we only need the cells in between.

ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))-1

becomes

6-1

and returns 5 in cell E16.

Get Excel *.xlsx file

formula to count cells between two values.xlsx

Count values category

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

Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

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

Excel categories

Comments (5)