SOLVED

Sum of values within overlapping date ranges

Copper Contributor

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 dateEnd dateNumber of nightsPatients DateNum of patients per night
01/04/202210/05/2022383 16/05/2022

 

17/05/202204/06/20221730 17/05/2022 
17/05/202214/06/202227100 18/05/2022 
01/07/202205/07/202230 19/05/2022 
     20/05/2022 
     21/05/2022 
6 Replies

@LorePC 

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

Is this the formula you are looking for?

@LorePC 

=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

@LorePC 

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

Thank you very much for your help. This is great!! It worked :)
Thank you very much for your help. Switched them as suggested and it worked :)
This formula also worked perfectly! Thank you very much :)
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@LorePC 

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

View solution in original post