SOLVED

New Contributor

# Sum of values within overlapping date ranges

Hello,

I hope you're well.

I was wondering if someone could help me with a formula to sum values within a date rage. Some of the date ranges overlap. Based on the data I have in columns A-D I would like to calculate the number of pax per night. I've tried the formula below in cell G2 but it didn't work (cells G4-G8 should return 130) . I believe I should be using a different function. Could someone kindly advise please? I'm using O365

=SUMIFS(\$D\$3:\$D\$6,\$A\$3:\$A\$6,">="&\$F5,\$B\$3:\$B\$6,"<"&\$F5)

 Start date End date Number of nights Patients Date Num of patients per night 01/04/2022 10/05/2022 38 3 16/05/2022 17/05/2022 04/06/2022 17 30 17/05/2022 17/05/2022 14/06/2022 27 100 18/05/2022 01/07/2022 05/07/2022 3 0 19/05/2022 20/05/2022 21/05/2022
6 Replies

# Re: Sum of values within overlapping date ranges

=SUMPRODUCT((F3>=\$A\$3:\$A\$6)*(F3<=\$B\$3:\$B\$6)*\$D\$3:\$D\$6)

Is this the formula you are looking for?

# Re: Sum of values within overlapping date ranges

=SUMIFS(\$D\$3:\$D\$6,\$A\$3:\$A\$6,"<="&F3,\$B\$3:\$B\$6,">="&F3)

Your formula calculates the same result if you switch "<" and ">".

best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Sum of values within overlapping date ranges

SUMIFS() shall work if change first condition on \$A\$3:\$A\$6,"<="&\$F5

# Re: Sum of values within overlapping date ranges

Thank you very much for your help. This is great!! It worked

# Re: Sum of values within overlapping date ranges

Thank you very much for your help. Switched them as suggested and it worked

# Re: Sum of values within overlapping date ranges

This formula also worked perfectly! Thank you very much