Oct 25 2021 09:41 AM
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 |
Oct 25 2021 10:40 AM
=SUMPRODUCT((F3>=$A$3:$A$6)*(F3<=$B$3:$B$6)*$D$3:$D$6)
Is this the formula you are looking for?
Oct 25 2021 10:52 AM
=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 ">".
Oct 25 2021 10:52 AM
SolutionSUMIFS() shall work if change first condition on $A$3:$A$6,"<="&$F5
Oct 25 2021 12:17 PM
Oct 25 2021 12:18 PM
Oct 25 2021 12:19 PM
Oct 25 2021 10:52 AM
SolutionSUMIFS() shall work if change first condition on $A$3:$A$6,"<="&$F5