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

#### mdmhal

##### New Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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?

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

Try this:

Book1
ABCDEF
1
2
3StartEnd
5xxx212/2/20213/2/2022
7xxx412/4/20213/4/2022
80xxx512/5/20213/5/2022
91xxx612/6/20213/6/2022
10xxx712/7/20213/7/2022
11xxx812/8/20213/8/2022
129
Live Project Data
Cell Formulas
RangeFormula
A8A8=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)
A9A9=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))

Eric,

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

Thank you, thank you, thank you!
Hal

Glad I could help! Thanks for the update. ?

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

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?

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

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

Book1
ABCDEF
1
2
3StartEnd
5xxx212/2/20213/2/2022
7xxx412/4/20213/4/2022
81xxx512/5/20213/5/2022
90xxx612/6/20213/6/2022
10xxx712/7/20213/7/2022
11xxx812/8/20213/8/2022
129
Live Project Data
Cell Formulas
RangeFormula
A8A8=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)
A9A9=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))

Replies
11
Views
2K
Replies
3
Views
729
Replies
1
Views
749
Replies
4
Views
690
Replies
1
Views
63

1,218,920
Messages
6,145,223
Members
450,602
Latest member
AceSpace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back