Summarise data accross months

[appolo]

Hi All

In the attached spread sheet, I have a date range which summarises into hours per day for this range. In the summary table I am trying to summarise these hours by month and year selected, is this possible to do?

Regards

[Ron Coderre]

Using your posted workbook,
this regular formula returns the total "Hours Required", by month/year and type

Please Login or Register  to view this content.

Copy that formula across and down through V6
...oh...and remove the extra space from "Won " in J5

Is that something you can work with?

[appolo]

Thanks Ron, it does help, but it groups the data by the initial month, I would like to get the data spread out across the date range, not sure if this can be done.

Regards

[Ron Coderre]

When Hours Required (60) is spread across several months (1-Jan-13 through 30-Mar-13) do you want those hours to count in each month?

[appolo]

Yes, for example if 60 hours are spread across 1 Jan 2013 through 20 mar 13, which works out at 0.77hrs/day, I would like it to spread these hours across the months, so that would be Jan = 24 hrs, feb = 21, and for March = 15

[Ron Coderre]

Try this ARRAY FORMULA, completed by Holding Down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

Please Login or Register  to view this content.

Copy M4 and paste into M5:M6
Copy M4:M6 and paste into N4:V6

Does that help?

[appolo]

Thanks Ron, this worked a treat, very efficient!