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
Similar Threads
