Monthly Totals with multiple date ranges

[Wheelie686]

I am trying to make a table of all vacations by month. Cells F21:F35 have the total days per request and I'd like to keep the total by month in Column B. I'd also like to only total them if Column G says Approved. I stopped in Feb 2024 but as more come in in future months, Columns A & B will get bigger.

I manually entered the totals in F2:F17 while I was trying various formulas but none seemed to give me the right answers.
I also highlighted one particular request in Row 34. There will be some requests that start in one month and end in another.

[russkris]

Hi Wheelie686,

So just looking the the Requests table, if you pop the following in Cell F19 and copy down

Please Login or Register  to view this content.

Unsure what the highlighted range in F2:F15 is for

[Wheelie686]

Hi russkris,
F2:F15 are the results I'm looking for. For instance
Jan 2023 has a total of 11 days (3 requests) when you look at the duration of all the Approved dates in the bottom table
Feb-Apr have none
May 2023 has 3
June has 2
And so on and so forth

So what I'm looking for is a formula for B2-B15

[HansDouwe]

Please try in B2 and copy down:

Please Login or Register  to view this content.

This is an array formule, please confirm the formula with Ctrl+Shift+Enter

[Wheelie686]

So something I found a little odd - it worked exactly as you planned in your document (which I changed to your name when I uploaded) but the exact same formula in mine does not... I can use your copy, which works, I just find it odd that it doesn't work in mine.

On a side note, would there be a way to update the formula to account for expansion beyond Row 19-33? As more months go by, Columns A&B will get longer, therefore pushing Row 19 down to 20 and beyond. Also as new requests come in, it will go beyond Row 33.

[JeteMc]

It would be better to put the requests data somewhere else so that it does not have to be moved each time that a new month is added in columns A:B.
That said one way to do what you want would be to convert the input range under the heading Requests into an Excel table.
Note that after the conversion I inserted some rows which moved tbl_Request down to row 21.
I also added a row at the bottom of the table and the formula automatically adjusted to both changes.
Let us know if you have any questions.

[HansDouwe]

In your book2 are the dates in column A of the wrong year (23 January 2024, 23 February 2024 and so on.
You need there dates in 2023 and not in 2024.

I've changed the dates in your book2 and the formula works perfect.

[Wheelie686]

Thank you JeteMc and HansDouwe!

[HansDouwe]

You are Welcome!

Thanks for the feedback and rep .

Glad to have helped.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.