Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values and unique numbers in column B.
If this is not what you are looking for then scroll down to see more examples.
Formula in cell E5:
Since there is only one instance of each search value in column B you only need a small formula to calculate the number of cells between the values.
Column A shows the cell count between the two values.
Explaining formula in cell E5
The formula finds the first value specified in cell E2 (45) in column B and returns the relative position of 45. It then continues with the second value given in cell E3.
The difference between the calculated relative positions is the number of cells between the values.
Step 1 - Find position of first value
The MATCH function returns the relative position of the value in cell E2 in cell range B2:B21.
MATCH(E2,$B$2:$B$21,0)
returns 6. Value 45 is the sixth value in the array.
Step 2 - Find position of second value
MATCH(E3,$B$2:$B$21,0)
returns 14. Value 115 is the 14th value in the array.
Step 3 - Subtract row numbers
MATCH(E2,$B$2:$B$21,0)-MATCH(E3,$B$2:$B$21,0)
becomes
6-14
and returns -8.
Step 4 - Remove sign
The ABS function converts negative numbers to positive numbers.
ABS(MATCH(E2,$B$2:$B$21,0)-MATCH(E3,$B$2:$B$21,0))-1
becomes
ABS(-8)-1
becomes
8-1 and returns 7 in cell E5.
Multiple values - two search values
The image above shows the second scenario, there are multiple instances of each search value in column B. This requires a somewhat more complicated formula to get the smallest number of cells between the two search values.
The order makes no difference, in other words, if search value 2 is found first makes no difference in this calculation.
Array formula in cell E5:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Explaining formula in cell E5
Step 1 - Calculate row numbers for first search value
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(B2:B25=E2, ROW(B2:B25), "")
returns {""; ""; ... ; 25}.
Step 2 - Calculate row numbers for second search value
The TRANSPOSE function rearranges values distributed vertically to horizontally.
TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), ""))
returns {"", "", ... , ""}.
Step 3 - Subtract first array with second array
IF(B2:B25=E2, ROW(B2:B25), "")-TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), ""))
returns an array too big to show here, however, an image would work if I replace all error values with a "-".
What does the image above tell us? It shows row numbers horizontally and vertically. The first search value (1) has its row numbers displayed vertically, there are four instances of the search value in column B, row 9, 14, 21 and 25.
The second search value har its row numbers displayed horizontally, it is found on row 11, 18 and 23. Example, the intersection of 9 and 11 shows -2. It means that 9-11 equals -2, the whole array shows the intersection between all instances of each search value.
This allows us to extract the smallest distance between two cells, or if you like the largest distance or any in between.
Step 4 - Convert negative values to positive values
The ABS function removes the minus sign from negative values.
ABS(IF(B2:B25=E2, ROW(B2:B25), "")-TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), "")))
Step 5 - Extract smallest number
The AGGREGATE function is better than the SMALL function, it also lets you ignore error values.
AGGREGATE(15, 6, ABS(IF(B2:B25=E2, ROW(B2:B25), "")-TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), ""))), 1)-1
becomes
2-1
and returns 1 in cell E5.
Multiple values - one search value
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)
Explaining formula in cell E2
There are two parts that calculate the position of the first instance and the second instance, the formula then subtracts the positions in order to get the distance.
Step 1 - Get position of second instance
SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1)+1)
becomes SMALL({1;"";"";4;"";"";7;"";"";"";"";"";"";14},2)
and returns 4. The second instance of 1 is found on row 4 (C5 is on row 4 in cell range $C$2:$C$15).
Step 2 - Get position of first instance
SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1))
becomes SMALL({1;"";"";4;"";"";7;"";"";"";"";"";"";14}, 1)
and returns 1. The first instance of 1 is found on row 1 (C2 is on row 1 in cell range $C$2:$C$15).
Step 3 - Subtract positions
SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1)+1)-SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1))-1
becomes
4-1-1
and returns 2 in cell G3.
How to remove #num errors
Count values category
This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Excel categories
17 Responses to “Count cells between specified values”
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
Paste image link to your comment.
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,
Get the 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
https://postimg.cc/Mcc7zvt4/7c681b03
I need to count the values in C row which meets criteria in A row.
For example, I want to count only C2:C6 which is 6.9 in row A. count values of C7:C12 which meets criteria as 7 in row A