Arghhhh!!! Need to sum for varying date ranges.

[mlibby]

I deeply appreciate any help you all can provide; thanks for running this site, it's a blessing for the rest of us. What I'm trying to do is sum the daily interest of a loan for any range of dates. Imagine this example:

1) In column A you have dates from 01/01/2009 through 12/31/2059, with each individual day showing, so literally thousands of cells.

2) In column B you have just random values corresponding to each date, such as $100,000 on 01/01/2009, $139,000 on 01/02/2009, $52 on 01/03/2009, and so forth. These values occupy as many cells as the dates, i.e. there's a cash flow (value) for every date.

3) I want to run a formula in column C that will sum a date range I specify. For example, if I want to sum all the daily values for each month (i.e. the 1st through the 30th), or if I want to sum all the daily values for the 15th of a month through the 14th of the following month. In other words, the loan can start on any date, and I need to sum the interest for each "monthly anniversary."

4) I'm thinking that the formula will have to be different to sum an actual entire month. For example, formulas to sum from, say, the 15th of one month through the 14th of the next month, or the 10th of one month through the 9th of the next month, might be similar.

However, as there is a varying number of days in most months, that formula might not work (though I might be misunderstanding myself) when trying to sum the values for an entire month, i.e. February 1st through 28th. I think it might be solved by some sort of "if," such as if day = 1, then sum whole month, otherwise some from date through same date of next month less the last date's cash flows?

I hope that's understanable for everyone. I'm still a bit of a novice. I would deeply appreciate anything you all could provide.

Best,

Matt

 

[Tom Urtis]

You are still not being clear but let's try to get this thing completed here.


For one thing, in the Account C section, on the row for date 03/29/09, how is it that you arrive at 3100 when 29 rows are being counted at $100 each and it should be 2900.

For another thing,

Either

You only care about 3 scenarios (day 1 of month, day 15 of month, day 30 of month) as you seem to depict here with Account A, B, and C and in your second post when you wrote:
"1) I want to sum the values of a 1 month period for every one of those periods. So, if I have a loan starting on the 15th of a month, I know that the cash flows for its monthly period will run from the 15th through the 14th of the following month. Thus, in column C, I need a formula that will go down the whole column, finding the 14th of each month, and then summing from the prior month's 15th through the 14th. Or, for example, if the loan starts on the 1st of a month, I need a formula that I can drop into the entire column C, that will sum the values for the actual month on the last day of every month."

or

You plan to essentially build 30 or 31 Account tables to accommodate any calendar day of the month can be a start date, because you in your fourth post you wrote:
"So, if a customer opens the card on the 10th of a month, his/her cycle will run through the 9th of the following month."



So...

I copied the 3 tables (Account A, B, and C) directly from this web page into a worksheet, and assuming...

- row 1 is the header row
- row 2 is where the data starts
- Table "Account A" occupies columns A:C
- Table "Account B" occupies columns D:F
- Table "Account C" occupies columns G:I

...these 3 formulas worked for me with the data and examples you gave, except that I further assumed you made a mistake with that 3100 in Account C for March 29, 2009 when it should be 2900.

In cell C2, enter and copy down as needed:
=IF(OR(MONTH(A3)=MONTH(A2),LEN(A3)=0),"",SUM(INDIRECT("B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),A:A,0)):INDIRECT("B"&ROW())))

In cell F2 enter and copy down as needed
=IF(DAY(D2)<>14,"",SUM(INDIRECT("E"&MATCH(DATE(YEAR(D2),MONTH(D2)-1,15),D:D,0)):INDIRECT("E"&ROW())))

In cell I2, enter and copy down as needed, accommodating February and leap years:
=IF(AND(MONTH(G2)<>2,DAY(G2)<>29),"",IF(MONTH(G2)<>2,SUM(INDIRECT("H"&MATCH(DATE(YEAR(G2),MONTH(G2)-1,IF(MONTH(G2)<>3,30,29)),G:G,0)):INDIRECT("H"&ROW())),IF(DAY(G2)=IF(MONTH(DATE(YEAR(G2),2,29))<>2,28,29),SUM(INDIRECT("H"&MATCH(DATE(YEAR(G2),MONTH(G2)-1,30),G:G,0)):INDIRECT("H"&ROW())),"")))



I caution you again that these formulas in tens or hundreds of thousands of cells might slow your workbook depending on your system resources. If it were me and I was needing 31 tables I'd design the thing using VBA instead of having all that dead formula weight hanging around, but these formulas worked with the tables and layout you presented.