Romi wrote:
Is there a quick formula to test whether a value falls between 2
values? I want to use the formula in another formula, such as
'sumif' the values that fall between a range of 2 values.
Thanks for your help.


Hi Romi,

If your number you want to test and sum are, for example, in the range
A3:A20 and you want to sum all the numbers greater than 15 and lesser than
20, to make your test you can write in B3 the following formula:

=IF(A3<20,IF(A315,1,),)

and then copy until B20. In this way your SUMIF will be:

=SUMIF(B3:B20,1,A3:A20)

But, instead of this two step procedure, you can use directly a formula with
the SUMPRODUCT function:

=SUMPRODUCT((A3:A2015)*(A3:A20<20)*A3:A20)

Finally, if you want to put the number for the test outside of the formula,
say in C2 and C3 (with C3C2), you can use the following:

=SUMPRODUCT((A3:A20C2)*(A3:A20<C3)*A3:A20)

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy