SOLVED

Sum of values within overlapping date ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-2882592%22%20slang%3D%22en-US%22%3ESum%20of%20values%20within%20overlapping%20date%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2882592%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20hope%20you're%20well.%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20someone%20could%20help%20me%20with%20a%20formula%20to%20sum%20values%20within%20a%20date%20rage.%20Some%20of%20the%20date%20ranges%20overlap.%20Based%20on%20the%20data%20I%20have%20in%20columns%20A-D%20I%20would%20like%20to%20calculate%20the%20number%20of%20pax%20per%20night.%20I've%20tried%20the%20formula%20below%20in%20cell%20G2%20but%20it%20didn't%20work%20(cells%20G4-G8%20should%20return%20130)%20.%20I%20believe%20I%20should%20be%20using%20a%20different%20function.%20Could%20someone%20kindly%20advise%20please%3F%20I'm%20using%20O365%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(%24D%243%3A%24D%246%2C%24A%243%3A%24A%246%2C%22%26gt%3B%3D%22%26amp%3B%24F5%2C%24B%243%3A%24B%246%2C%22%26lt%3B%22%26amp%3B%24F5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22602%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3EStart%20date%3C%2FTD%3E%3CTD%20width%3D%2277%22%3EEnd%20date%3C%2FTD%3E%3CTD%20width%3D%22113%22%3ENumber%20of%20nights%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPatients%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2277%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%22130%22%3ENum%20of%20patients%20per%20night%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2F04%2F2022%3C%2FTD%3E%3CTD%3E10%2F05%2F2022%3C%2FTD%3E%3CTD%3E38%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E16%2F05%2F2022%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%2F05%2F2022%3C%2FTD%3E%3CTD%3E04%2F06%2F2022%3C%2FTD%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E17%2F05%2F2022%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%2F05%2F2022%3C%2FTD%3E%3CTD%3E14%2F06%2F2022%3C%2FTD%3E%3CTD%3E27%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E18%2F05%2F2022%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2F07%2F2022%3C%2FTD%3E%3CTD%3E05%2F07%2F2022%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E19%2F05%2F2022%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E20%2F05%2F2022%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E21%2F05%2F2022%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2882592%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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