Counting the number of cells between 2 seperate cells
 

Hi there,
I have a table that looks like this (2 rows, with 18 numbers each)

32,15,19,4,21,2,25,17,34,6,27,13,36,11,30,8,23,26
10,5,24,16,33,1,20,14,31,9,22,18,29,7,28,12,35,3

(each number is in it's own cell). The table is located in cells I4:AR5.
What I'm looking for is if cell A14 gives the number 19, then cell A15 gives
the number 27, then a formula in cell C15 counts how many cells/numbers are
between 19 and 27 in the table above (starting at the number to the right of
19, so do not include 19, but include 27). The answer should be 8. Also,
the same would apply to the numbers on the bottom. And, I need to be able to
count the cells between top row and bottom row in the same fashion (so if A14
was 32, and A15 was 16, it would count 3). If we need to build more than one
table, that's okay.

Thanks for all the help! I'm stumped on this one!
Matt

Counting the number of cells between 2 seperate cells
 

=match(27,i4:AR5,0)-match(19,i4:AR5,0)

"mpenkala" wrote:

Hi there,
I have a table that looks like this (2 rows, with 18 numbers each)

32,15,19,4,21,2,25,17,34,6,27,13,36,11,30,8,23,26
10,5,24,16,33,1,20,14,31,9,22,18,29,7,28,12,35,3

(each number is in it's own cell). The table is located in cells I4:AR5.
What I'm looking for is if cell A14 gives the number 19, then cell A15 gives
the number 27, then a formula in cell C15 counts how many cells/numbers are
between 19 and 27 in the table above (starting at the number to the right of
19, so do not include 19, but include 27). The answer should be 8. Also,
the same would apply to the numbers on the bottom. And, I need to be able to
count the cells between top row and bottom row in the same fashion (so if A14
was 32, and A15 was 16, it would count 3). If we need to build more than one
table, that's okay.

Thanks for all the help! I'm stumped on this one!
Matt

Counting the number of cells between 2 seperate cells
 

Hey Nathan, thanks for your suggestion, but it didn't work. I get #N/A
everytime. I tried switching the 27 and 19, different ranges, always get the
NA error. Any thoughts?

Thanks,
Matt


"nathan_savidge" wrote:

=match(27,i4:AR5,0)-match(19,i4:AR5,0)

"mpenkala" wrote:

Hi there,
I have a table that looks like this (2 rows, with 18 numbers each)

32,15,19,4,21,2,25,17,34,6,27,13,36,11,30,8,23,26
10,5,24,16,33,1,20,14,31,9,22,18,29,7,28,12,35,3

(each number is in it's own cell). The table is located in cells I4:AR5.
What I'm looking for is if cell A14 gives the number 19, then cell A15 gives
the number 27, then a formula in cell C15 counts how many cells/numbers are
between 19 and 27 in the table above (starting at the number to the right of
19, so do not include 19, but include 27). The answer should be 8. Also,
the same would apply to the numbers on the bottom. And, I need to be able to
count the cells between top row and bottom row in the same fashion (so if A14
was 32, and A15 was 16, it would count 3). If we need to build more than one
table, that's okay.

Thanks for all the help! I'm stumped on this one!
Matt