Conditional Suming based on variable is ithe condition

[Peter]

I have build a financial business model. I have a schedule of various
agreement for monthly payments(see aprox format below).

A B C D E F
---------------------------------------------------
| | | First | Last | Pmy |...|
| Name | Freq | Pmt | Pmt | Amt |...|
---------------------------------------------------
| Agree #1 | Monthly |1/30/04 |12/30/04|$1,200 |...|
---------------------------------------------------
| Agree #2 | Monthly |5/15/04 |11/15/05| $500 |...|
---------------------------------------------------
---- many more lines follow below ------

Then I want to create a lookup table that will give me the total
commitments in a give month. This is for use in a cash flow and
prospective I/S.

X Y
-------------------------
Date Total Pmt
-------------------------
6/1/2004 (sum colum E where month&year(X) > Month&Year(C) AND
month&year
(X) < Month&Year(D)
6/1/2004 ^^ same as above
7/1/2004 ^^
8/1/2004 ^^
9/1/2004 ^^
..... ^^

the problem is that sumif does not work with a variable in the
condition area. Any seguestions to achive this desired result. I can
write a VBA function to do this but I would rather avoid it.

Thank you.

 

[Kevin H. Stecyk]

Peter wrote...

I have build a financial business model. I have a schedule of various
agreement for monthly payments(see aprox format below).

A B C D E F
---------------------------------------------------
| | | First | Last | Pmy |...|
| Name | Freq | Pmt | Pmt | Amt |...|
---------------------------------------------------
| Agree #1 | Monthly |1/30/04 |12/30/04|$1,200 |...|
---------------------------------------------------
| Agree #2 | Monthly |5/15/04 |11/15/05| $500 |...|
---------------------------------------------------
---- many more lines follow below ------

Then I want to create a lookup table that will give me the total
commitments in a give month. This is for use in a cash flow and
prospective I/S.

X Y
-------------------------
Date Total Pmt
-------------------------
6/1/2004 (sum colum E where month&year(X) > Month&Year(C) AND
month&year
(X) < Month&Year(D)
6/1/2004 ^^ same as above
7/1/2004 ^^
8/1/2004 ^^
9/1/2004 ^^
..... ^^

the problem is that sumif does not work with a variable in the
condition area. Any seguestions to achive this desired result. I can
write a VBA function to do this but I would rather avoid it.

Peter,

Hi Peter,

Here's what I did.

I have
* C2:C5 being monthly start dates.
* D25 being monthly end dates
* E2:E5 being monthly payments
* C8 being the date of interest (how much is rec'd that month?)

Formula:
=SUMPRODUCT((C8>=C2:C5)*(C8<D25)*(E2:E5))

If you have annual values, I am sure you can adapt the formula to suit.

Also, I have chosen the equal or greater than the first date, but less than
the second date. You should make sure that reflects your model.

Hope this helps.

Regards,
Kevin

 

[duane]

how about

=SUMPRODUCT(($C$7:$C$8<=A11)*$F$7:$F$8)-SUMPRODUCT((A11>$D$7:$D$8)*$F$7:$F$8)

where the 1st sumproduct adds up all occurances where the start mont
is <= to the month in question, and the 2nd sumproduct subtracts an
cases where the month in question is > than end month

in my example column c was start mont, column d was end month, an
column f was monthly rent. column a is a given mont