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.

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

Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite helpful.

For my current particular issue, though, the values I'm trying to test are
dates (eg data whose date, in column B, falls within the range '01/01/2009
thru 06/30/2009'), while the corresponding values I want to sum are in
another column. So I'll have to test out your example to see, as I'm not yet
comfortable enough with SUMPRODUCT and not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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

Hi Romi,

Thanks for your feedback.

If you have dates in column B, for example from B3 to B20, and the
corresponding number to sum in column A (A3:A20) and you have your smallest
date (for example 01/01/2009) in G1 and the biggest (for example 06/30/2009)
in H1the formula becomes:

=SUMPRODUCT((B3:B20G1)*(B3:B20<H1)*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

Romi wrote:
Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite helpful.

For my current particular issue, though, the values I'm trying to
test are dates (eg data whose date, in column B, falls within the
range '01/01/2009 thru 06/30/2009'), while the corresponding values I
want to sum are in another column. So I'll have to test out your
example to see, as I'm not yet comfortable enough with SUMPRODUCT and
not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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

Thank-you, Franz.
Your suggestion works well.

Is there a way to get it to work without having to first sort the data
records by date order so the formula need not point to a specific cell? I
tried putting the and < date designations within double-quotes like I would
if using SUMIF function, but it didn't work in the SUMPRODUCT function.

Romi

"Franz Verga" wrote:

Hi Romi,

Thanks for your feedback.

If you have dates in column B, for example from B3 to B20, and the
corresponding number to sum in column A (A3:A20) and you have your smallest
date (for example 01/01/2009) in G1 and the biggest (for example 06/30/2009)
in H1the formula becomes:

=SUMPRODUCT((B3:B20G1)*(B3:B20<H1)*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

Romi wrote:
Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite helpful.

For my current particular issue, though, the values I'm trying to
test are dates (eg data whose date, in column B, falls within the
range '01/01/2009 thru 06/30/2009'), while the corresponding values I
want to sum are in another column. So I'll have to test out your
example to see, as I'm not yet comfortable enough with SUMPRODUCT and
not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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

Hi Romi,

you don't need to sort the data by date. It wroks without any kind of
sorting.

--
(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

Romi wrote:
Thank-you, Franz.
Your suggestion works well.

Is there a way to get it to work without having to first sort the data
records by date order so the formula need not point to a specific
cell? I tried putting the and < date designations within
double-quotes like I would if using SUMIF function, but it didn't
work in the SUMPRODUCT function.

Romi

"Franz Verga" wrote:

Hi Romi,

Thanks for your feedback.

If you have dates in column B, for example from B3 to B20, and the
corresponding number to sum in column A (A3:A20) and you have your
smallest date (for example 01/01/2009) in G1 and the biggest (for
example 06/30/2009) in H1the formula becomes:

=SUMPRODUCT((B3:B20G1)*(B3:B20<H1)*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

Romi wrote:
Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite helpful.

For my current particular issue, though, the values I'm trying to
test are dates (eg data whose date, in column B, falls within the
range '01/01/2009 thru 06/30/2009'), while the corresponding values
I want to sum are in another column. So I'll have to test out your
example to see, as I'm not yet comfortable enough with SUMPRODUCT
and not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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

HiRomi,

First: the formula works roperly without any sorting;

Second: if you want to insert directly in the formula the lower and upper
dates, you can use this version:

=SUMPRODUCT((B3:B20VALUE("01/01/2009"))*(B3:B20<VALUE("06/30/2009"))*A3:A20)

but I would suggest to use the previous version, because it is always better
to have outside of formulas any kind of values: in this way it is much more
easier to change the limits and to check what the formula is doing, without
looking inside the formula.

--
(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
Romi wrote:
Thank-you, Franz.
Your suggestion works well.

Is there a way to get it to work without having to first sort the data
records by date order so the formula need not point to a specific
cell? I tried putting the and < date designations within
double-quotes like I would if using SUMIF function, but it didn't
work in the SUMPRODUCT function.

Romi

"Franz Verga" wrote:

Hi Romi,

Thanks for your feedback.

If you have dates in column B, for example from B3 to B20, and the
corresponding number to sum in column A (A3:A20) and you have your
smallest date (for example 01/01/2009) in G1 and the biggest (for
example 06/30/2009) in H1the formula becomes:

=SUMPRODUCT((B3:B20G1)*(B3:B20<H1)*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

Romi wrote:
Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite helpful.

For my current particular issue, though, the values I'm trying to
test are dates (eg data whose date, in column B, falls within the
range '01/01/2009 thru 06/30/2009'), while the corresponding values
I want to sum are in another column. So I'll have to test out your
example to see, as I'm not yet comfortable enough with SUMPRODUCT
and not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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

Hi Franz,
A million thanks!
Romi

"Franz Verga" wrote:

HiRomi,

First: the formula works roperly without any sorting;

Second: if you want to insert directly in the formula the lower and upper
dates, you can use this version:

=SUMPRODUCT((B3:B20VALUE("01/01/2009"))*(B3:B20<VALUE("06/30/2009"))*A3:A20)

but I would suggest to use the previous version, because it is always better
to have outside of formulas any kind of values: in this way it is much more
easier to change the limits and to check what the formula is doing, without
looking inside the formula.

--
(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
Romi wrote:
Thank-you, Franz.
Your suggestion works well.

Is there a way to get it to work without having to first sort the data
records by date order so the formula need not point to a specific
cell? I tried putting the and < date designations within
double-quotes like I would if using SUMIF function, but it didn't
work in the SUMPRODUCT function.

Romi

"Franz Verga" wrote:

Hi Romi,

Thanks for your feedback.

If you have dates in column B, for example from B3 to B20, and the
corresponding number to sum in column A (A3:A20) and you have your
smallest date (for example 01/01/2009) in G1 and the biggest (for
example 06/30/2009) in H1the formula becomes:

=SUMPRODUCT((B3:B20G1)*(B3:B20<H1)*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

Romi wrote:
Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite helpful.

For my current particular issue, though, the values I'm trying to
test are dates (eg data whose date, in column B, falls within the
range '01/01/2009 thru 06/30/2009'), while the corresponding values
I want to sum are in another column. So I'll have to test out your
example to see, as I'm not yet comfortable enough with SUMPRODUCT
and not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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

Hi Romi,

thank you for your feedback.


--
Ciao


Franz Verga from Italy


Romi wrote:
Hi Franz,
A million thanks!
Romi

"Franz Verga" wrote:

HiRomi,

First: the formula works roperly without any sorting;

Second: if you want to insert directly in the formula the lower and
upper dates, you can use this version:

=SUMPRODUCT((B3:B20VALUE("01/01/2009"))*(B3:B20<VALUE("06/30/2009"))*A3:A20)

but I would suggest to use the previous version, because it is
always better to have outside of formulas any kind of values: in
this way it is much more easier to change the limits and to check
what the formula is doing, without looking inside the formula.

--
(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
Romi wrote:
Thank-you, Franz.
Your suggestion works well.

Is there a way to get it to work without having to first sort the
data records by date order so the formula need not point to a
specific cell? I tried putting the and < date designations within
double-quotes like I would if using SUMIF function, but it didn't
work in the SUMPRODUCT function.

Romi

"Franz Verga" wrote:

Hi Romi,

Thanks for your feedback.

If you have dates in column B, for example from B3 to B20, and the
corresponding number to sum in column A (A3:A20) and you have your
smallest date (for example 01/01/2009) in G1 and the biggest (for
example 06/30/2009) in H1the formula becomes:

=SUMPRODUCT((B3:B20G1)*(B3:B20<H1)*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

Romi wrote:
Hi Franz,
Thanks so much for such quick reply.
I never knew about 'SUMPRODUCT' function, so you were quite
helpful.

For my current particular issue, though, the values I'm trying to
test are dates (eg data whose date, in column B, falls within the
range '01/01/2009 thru 06/30/2009'), while the corresponding
values I want to sum are in another column. So I'll have to test
out your example to see, as I'm not yet comfortable enough with
SUMPRODUCT and not very good with date formulas.

Thanks again!
Romi

"Franz Verga" wrote:

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