Excel formula to test a value BETWEEN 2 values?

Discussion in 'Microsoft Excel Misc' started by Romi, Jul 28, 2009.

  1. Romi

    Romi Guest

    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, Jul 28, 2009
    #1
    1. Advertisements

  2. Romi

    Franz Verga Guest

    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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:

    =SUMPRODUCT((A3:A20>C2)*(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
     
    Franz Verga, Jul 28, 2009
    #2
    1. Advertisements

  3. Romi

    Romi Guest

    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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:
    >
    > =SUMPRODUCT((A3:A20>C2)*(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
    >
    >
     
    Romi, Jul 29, 2009
    #3
  4. Romi

    Franz Verga Guest

    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:B20>G1)*(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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:
    >>
    >> =SUMPRODUCT((A3:A20>C2)*(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
     
    Franz Verga, Jul 29, 2009
    #4
  5. Romi

    Romi Guest

    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:B20>G1)*(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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:
    > >>
    > >> =SUMPRODUCT((A3:A20>C2)*(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

    >
    >
    >
     
    Romi, Jul 29, 2009
    #5
  6. Romi

    Franz Verga Guest

    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:B20>G1)*(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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:
    >>>>
    >>>> =SUMPRODUCT((A3:A20>C2)*(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
     
    Franz Verga, Jul 29, 2009
    #6
  7. Romi

    Franz Verga Guest

    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:B20>VALUE("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:B20>G1)*(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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:
    >>>>
    >>>> =SUMPRODUCT((A3:A20>C2)*(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
     
    Franz Verga, Jul 29, 2009
    #7
  8. Romi

    Romi Guest

    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:B20>VALUE("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:B20>G1)*(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(A3>15,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:A20>15)*(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 C3>C2), you can use the following:
    > >>>>
    > >>>> =SUMPRODUCT((A3:A20>C2)*(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

    >
    >
    >
     
    Romi, Jul 29, 2009
    #8
  9. Romi

    Franz Verga Guest

    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:B20>VALUE("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:B20>G1)*(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(A3>15,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:A20>15)*(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 C3>C2), you can use the
    >>>>>> following:
    >>>>>>
    >>>>>> =SUMPRODUCT((A3:A20>C2)*(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
     
    Franz Verga, Jul 29, 2009
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. @(none)

    ignore this - test that couldn't go to nntp.test

    @(none), Jan 23, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    309
    David McRitchie
    Jan 23, 2004
  2. jseabold
    Replies:
    1
    Views:
    286
    Dave Peterson
    Jan 30, 2006
  3. Guest
    Replies:
    5
    Views:
    423
    Guest
    May 2, 2007
  4. Guest
    Replies:
    8
    Views:
    172
    Bob Phillips
    Jun 3, 2007
  5. Jeff

    Can you help test our Excel test?

    Jeff, Dec 7, 2009, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    215
    JLatham
    Dec 7, 2009
Loading...

Share This Page