@ > Home > Contents > Use 1 instead of 2 interval boundaries Excel 97+

=SUMPRODUCT(--(\$A\$1:\$A\$999>20),--(\$A\$1:\$A\$999<90),\$B\$1:\$B\$999)

contains an interval for \$A\$1:\$A\$999 which therefore has to be mentioned twice.

To avoid this, shift this interval symmetrically around zero. Here we need a shift of -55, to reach a position of ]-35,35[. With the following, we can omit \$A\$1:\$A\$999 once:

=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999-55)<35),\$B\$1:\$B\$999)

That is: We use ABS() to treat the symmetric negative/positive side equally.

It is shorter - and faster! The gain of speed is between 10% and 30%. If you need more speed, read 5038.htm. ABS() can play this role in many places.

To use it generally (f.e. by using references instead of values) regard the following two steps:

=SUMPRODUCT(--(\$A\$1:\$A\$999>LOWER),--(\$A\$1:\$A\$999<UPPER),\$B\$1:\$B\$999)
=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999-(UPPER+LOWER)/2)<(UPPER-LOWER)/2),\$B\$1:\$B\$999)
=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999*2-UPPER-LOWER)<UPPER-LOWER),\$B\$1:\$B\$999)

Well - we lost the advantage "it gets shorter" here. The reason is that \$A\$1:\$A\$999 is a rather simple reference. But often, it is more complicated, and then ABS() will amazingly abbreviate it.

Inclusive interval ]a,b]:

More important for practical purposes is the following case to ensure nothing gets left out (see the red changes):

=SUMPRODUCT(--(\$A\$1:\$A\$999>20),--(\$A\$1:\$A\$999<=90),\$B\$1:\$B\$999)

Here the likewise transformation

=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999-55)<=35),\$B\$1:\$B\$999)

would be wrong! It would include 20 while it has to be excluded, because it "belongs" to a preceding interval.

To solve the problem, subtract half of the smallest possible value (f.e 1 Cent, meaning 0,5 Cent) additionally:

=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999-55.005)<35),\$B\$1:\$B\$999)

The boundaries now (here 20.005 and 90.005) are "values impossible". This means, even by using the exclusive "<" we catch them all. But: You are responsible to get the values cent-rounded (here referring to the money example)!

=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999*2-110.01)<70),\$B\$1:\$B\$999)

is equal to the above but multiplied by 2. We need this for the last, reference based, formula:

=SUMPRODUCT(--(ABS(\$A\$1:\$A\$999*2-UPPER-LOWER-smallestUnit)<UPPER-LOWER),\$B\$1:\$B\$999)

where smallestUnit = 0.01.

Why 0.01? Why not 0.005? To abbreviate the condition, we multiplied it simply by 2.