Date Series Grouped into Monthly Totals?

crayhead

New Member
Joined
Jun 6, 2016
Messages
10
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:


FirstLastStartEndTotalRental CostTax
JohnSmith5/45/6662.6530038.25
RachelRogers5/306/2998.7655570.76

<tbody>
</tbody>

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:

JanFebMarAprMay
Nights Stayed
Rental Income
$/Night
Taxes Collected

<tbody>
</tbody>


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 :confused: 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.;)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top