Calculating summed Value against 2 sets of date range Criteria

[hamish100]

Vacation Time.xlsx
Vacation time.JPG

Looking to calculate the sum of vacation days per month against a date range criteria.Formula will need the account for the assignment of right allocation of days between date ranges which start on one month and finish on another.

[jhren]

Will this suffice....

There is no provision for vacations which which start and end one or more full calendar months apart (e.g. starts in January, ends in March) but will be recorded, just all under the full month(s).

[hamish100]

Hello Jhren. the formula works however I was basically looking to use sumifs function. C2 = should sum D6 thru D8 if dates in A2:B2 Match dates in B6:B8,C6:C8. Is there a way to create the calculation using sumifs?

[jhren]

It can be done... but it will likely end up being a rather lengthy formula. It wouldn't be too difficult if each person's start and end dates are in the same month (assuming this is the periods which you are trying to determine). But it appears there is occasion where a person's start and end date can be in two different calendar months, where their number of days off will be split... and that is what makes it difficult with just using a one-cell formula.

[hamish100]

Okay. I appreciate the answer.