Sumifs the sum range activity from start to end date lands within 7-14 days from today

[mdmhal]

Hi dream team,

I need help with rectifying a formula. I currently have:

=SUMIFS('Live Project Data'!$D$4:$D$1001,'Live Project Data'!$E$4:$E$1001,">="&TODAY()+7,'Live Project Data'!$E$4:$E$1001,"<"&TODAY()+14,'Live Project Data'!$C$4:$C$1001,A6)

Where:
D4:D1001 - is the sum range
E4:E1001 - is a column of project start dates
C4:C1001 - is a column of various words from a drop down list, an example being "CAD". In this instance A6 = "CAD"

I also have a column of project end dates within my date source F4:F1001

I'm trying to make a sum of the D4:D1001 range, when a project has activity 7-14 days from today and contains "CAD" in C4:C1001. However as in the above formula I am calculating from a start date and not within the range of dates from project start to project end I am getting false readings.

For example today being 04/01/22

A project start date of 01/12/21 and end date of 01/03/22 would have activity within 7-14 days from today but it falls out of my formula calculation.

Hope that makes sense - Very appreciative of any help....

Many thanks,
Hal

 

[Eric W]

Welcome to the MrExcel forum!

Your formula is only looking to see if the start date is in the 7-day range. You need to see if the project range (start date to end date) overlaps the 7-day range. To do that, you need to include the project end date as part of the formula (and it's more complicated). Do you have a column with the end date in it?

 

[mdmhal]

Welcome to the MrExcel forum!

Your formula is only looking to see if the start date is in the 7-day range. You need to see if the project range (start date to end date) overlaps the 7-day range. To do that, you need to include the project end date as part of the formula (and it's more complicated). Do you have a column with the end date in it?

Thanks for the reply Eric, yes that's exactly right. I'm just unsure of how to do this.

I do have a column with the end dates in my project data. It is F4:F1001.

Any ideas?

Thanks,
Hal

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F
1
2
3					Start	End
4			CAD	1	12/1/2021	3/1/2022
5			xxx	2	12/2/2021	3/2/2022
6	CAD		xxx	3	12/3/2021	3/3/2022
7			xxx	4	12/4/2021	3/4/2022
8	0		xxx	5	12/5/2021	3/5/2022
9	1		xxx	6	12/6/2021	3/6/2022
10			xxx	7	12/7/2021	3/7/2022
11			xxx	8	12/8/2021	3/8/2022
12				9
Live Project Data

Cell Formulas
Range	Formula
A8	A8	=SUMIFS('Live Project Data'!$D$4:$D$1001,'Live Project Data'!$E$4:$E$1001,">="&TODAY()+7,'Live Project Data'!$E$4:$E$1001,"<"&TODAY()+14,'Live Project Data'!$C$4:$C$1001,A6)
A9	A9	=SUMPRODUCT('Live Project Data'!$D$4:$D$1001,SIGN((TODAY()+7>='Live Project Data'!$E$4:$E$1001)*(TODAY()+7<='Live Project Data'!$F$4:$F$1001)+(TODAY()+14>='Live Project Data'!$E$4:$E$1001)*(TODAY()+14<='Live Project Data'!$F$4:$F$1001)),--('Live Project Data'!$C$4:$C$1001=A6))

 

[mdmhal]

Eric,

Thank you so much, that works. Your help really means the world to me.

Thank you, thank you, thank you!
Hal

 

[Eric W]

Glad I could help! Thanks for the update. ?

 

[mdmhal]

Eric,

I've used this formula and its worked a treat.

However I also tried to find the sum within 14-42 days from today by swapping your formula for the below

=SUMPRODUCT('Live Project Data'!$D$4:$D$1001,SIGN((TODAY()+14>='Live Project Data'!$E$4:$E$1001)*(TODAY()+14<='Live Project Data'!$F$4:$F$1001)+(TODAY()+42>='Live Project Data'!$E$4:$E$1001)*(TODAY()+42<='Live Project Data'!$F$4:$F$1001)),--('Live Project Data'!$C$4:$C$1001=A6))

For some reason the results didn't come out correct.... in fact they mirrored the results of your original formula with the 7-14 days. Any ideas why this is?

Thanks,
Hal

 

[mdmhal]

Eric,

I've found an error unfortunately, where projects both start and end within the 7-14 days from today period, their value is not included in the sum....

Any ideas how to rectify this?

 

[mdmhal]

Eric,

I've realised the error mentioned above is what made the formula that I tried for the 14-42 days not work, as all of the project end dates fell within this time range, so if you're able to solve the above, then we're in business!

Many thanks,
Hal

 

[Eric W]

I'm going to need an example. The one I created works:

Book1
	A	B	C	D	E	F
1
2
3					Start	End
4			CAD	1	1/12/2022	1/14/2022
5			xxx	2	12/2/2021	3/2/2022
6	CAD		xxx	3	12/3/2021	3/3/2022
7			xxx	4	12/4/2021	3/4/2022
8	1		xxx	5	12/5/2021	3/5/2022
9	0		xxx	6	12/6/2021	3/6/2022
10			xxx	7	12/7/2021	3/7/2022
11			xxx	8	12/8/2021	3/8/2022
12				9
Live Project Data

Cell Formulas
Range	Formula
A8	A8	=SUMIFS('Live Project Data'!$D$4:$D$1001,'Live Project Data'!$E$4:$E$1001,">="&TODAY()+7,'Live Project Data'!$E$4:$E$1001,"<"&TODAY()+14,'Live Project Data'!$C$4:$C$1001,A6)
A9	A9	=SUMPRODUCT('Live Project Data'!$D$4:$D$1001,SIGN((TODAY()+7>='Live Project Data'!$E$4:$E$1001)*(TODAY()+7<='Live Project Data'!$F$4:$F$1001)+(TODAY()+14>='Live Project Data'!$E$4:$E$1001)*(TODAY()+14<='Live Project Data'!$F$4:$F$1001)),--('Live Project Data'!$C$4:$C$1001=A6))