Sum up values that falls between two specific dates

[Judicator]

Hello

Please consider this scenario. I have two dates: let's call them
receive date and disbursement date. In between them I receive
installments and I disburse some as well.

Receive Date Received Amount Disbursement Date
------------ --------------- ------------------
07 May 100000000000 11 May
15 May 100000000000 26 May
22 May 100000000000 26 May

Now the question is, how do I sum up the installments I receive
between two disbursement dates, that is, between 11th and 26th of May?

Thank you for your help.

 

[Guest]

Hi Judicato
Assuming your values to be totalled are in column B (B2:B20), the disbursement dates are in Column C (C2:C20) and the two dates between which u want the totalling done are in F4 and F5 (these dates are inclusive in the formula, but u can update the formula to get the desired results).

=SUM((B2:B20)*(C2:C20>=F4)*(C2:C20<=F5)

After entering the formula, do not press only <Enter> press <Cntrl><Shift><Enter> which will turn the formula into an array formula. The formula will now appear like this

{=SUM((B2:B20)*(C2:C20>=F4)*(C2:C20<=F5))

This will give u the desired results

Sukhjee


----- Judicator wrote: ----

Hello :

Please consider this scenario. I have two dates: let's call the
receive date and disbursement date. In between them I receiv
installments and I disburse some as well

Receive Date Received Amount Disbursement Dat
------------ --------------- -----------------
07 May 100000000000 11 May
15 May 100000000000 26 Ma
22 May 100000000000 26 May

Now the question is, how do I sum up the installments I receiv
between two disbursement dates, that is, between 11th and 26th of May

Thank you for your help

 

[Frank Kabel]

Hi
alternatve solution (non array entered):
=SUMPRODUCT(--(A1:A100>=DATE(2004,5,11)),--(A1:A100<=DATE(2004,5,26)),B
1:B100)

 

[TomMagee]

Frank


on your non-array solution what does the " - - " mean ?


To

 

[Frank Kabel]

Hi
this coerces the boolean values to real numbers (TRUE=1/FALSE=0)

 

[Chip Pearson]

Tom,

The comparisons (<=. >=) in the SUMPRODUCT function return
Boolean values, i.e., TRUE or FALSE. These need to be converted
to their numeric equivalents (1 or 0) to be multiplied by
SUMPRODUCT. The double negation is the same as multiplying the
array by -1 twice, which will convert the TRUE/FALSE values to
1/0 values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Judicator]

Thank you everyone