Counting rows whit various conditions

[Bowman]

Hello,

I have two columns with numeric values A & B, and i want to calculate in to
a C column, for each row, how many rows in the table meet that their A value
is between ( (ActualRow A value-1) and (ActualRow A value+1) ) and B value
between ((ActualRow B value-1) and (ActualRow B value+1)) meeting both
condition simultaneously.

example:

A-------- B----- C result column
///// ////// //////////////////
10 11 1
11 12 2
12 13 2
13 14 2
12 15 1

COUNT.IF generates uncorrect results
any idea???....


Thank you very much

 

[JE McGimpsey]

I have two columns with numeric values A & B, and i want to calculate in to
a C column, for each row, how many rows in the table meet that their A value
is between ( (ActualRow A value-1) and (ActualRow A value+1) ) and B value
between ((ActualRow B value-1) and (ActualRow B value+1)) meeting both
condition simultaneously.

example:

A-------- B----- C result column
///// ////// //////////////////
10 11 1
11 12 2
12 13 2
13 14 2
12 15 1

COUNT.IF generates uncorrect results
any idea???....

From your description, it sounds like you want to have a formula in C2
that calculates how many rows in the table A is between 9 and 11, and B
is between 10 and 12.

If that's the case, this will work for, say, cell C2:

=SUMPRODUCT(--(ABS($A$1:$A$100-$A2)<=1),--(ABS($B$1:$B$100-$B2)<=1))

which you could copy down.

However, that doesn't produce the values you list in your result column,
so I probably didn't understand what you were looking for. For instance,
there are two rows for which column A = 10 ± 1 and B=11 ± 1 (row 2
itself, of course, and row 3).