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

 

[mdmhal]

Eric,

The formula you have in A9 very nearly works in my sheet hence my prior excitement.

It sums all figures, just as I'd like, provided the project has a start date prior to the 7-14 day period and it runs into/through that period. Or indeed if the start day is within the 7-14 day period but the end date is beyond it.

HOWEVER when both the start date and the end date are within that 7-14 period the number in cells D4:D1001 is not included in the sum. I am looking for this to be included also.

I believe this is demonstrated in your sheet and is why your total in cell A9 is zero not one, as the dates in E4 and F4 are both within the 7-14 days from today period. As an example I think if you were to change say E4 for 1/1/2022 then cell A9 would read one??

Does that make sense?

 

[Eric W]

Oops! ? My bad, I got a few of the pieces in the wrong order. Try this:

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			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
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)+('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))

 

[mdmhal]

That looks to be the ticket!

Thank you again Eric, especially for sticking in there with me!

All the best my friend!

Hal