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