Date Series Grouped into Monthly Totals?

[crayhead]

Hi Excel Wizards,

I am working on a spreadsheet to automate some accounting and metrics functions for a rental property. I have a sheet that I want to populate titled "Income and Taxes" that will aggregate those sums from another sheet titled "Payments". The "Payments" sheet is manually updated from various income sources and includes the date range of the particular renters'/renter's stay in two columns:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Total[/TD]
[TD]Rental Cost[/TD]
[TD]Tax[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]5/4[/TD]
[TD]5/6[/TD]
[TD]662.65[/TD]
[TD]300[/TD]
[TD]38.25[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD]Rogers[/TD]
[TD]5/30[/TD]
[TD]6/2[/TD]
[TD]998.76[/TD]
[TD]555[/TD]
[TD]70.76[/TD]
[/TR]
</tbody>[/TABLE]

There are other numbers, including two columns I added to calculate nights stayed (=B4-B3) and $/Night (=B6/(B4-B3). Don't think those will help here, but mentioning in case.

As you can see, some dates will cross monthly ranges. If I need to just group those into the "Start" or "End" month numbers, so be it. The "Income and Taxes" sheet I want to populate will look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]Nights Stayed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rental Income[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$/Night[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Taxes Collected[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd like for the formula to do all that for me (As all good formulas should!) and imagine there are several ways to skin this cat but couldn't get traction with a variety of =IF (COUNTIF, SUMIF) functions because of the two separate date columns and the multiple month issue. If one of you whizkids could get me started, I am sure I can mess and tweak from there.

Thanks in advance for your assistance. I am using the current Office 365 Annual Subscription version of Excel, whatever that is.

 

[lennywatson]

If you can live with bundling the total payment into start or end months:

If D is end date,
Add a helper column that lists the month: =TEXT(D2,"mmm") (say that's in column H)
And then: =SUMIF(H2:H11,"May",E2:E11) (if E is the total rent paid column)
(and "Jan" "Feb" "Mar" etc)

Does that make sense?

 

[crayhead]

Lenny, that does make sense and is a reasonable solution. Anyone else have a different way to extract the dates - particularly if it gets dates exactly and not just lumped into the end month?