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

mdmhal

New Member
Joined
Jan 4, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,209
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

New Member
Joined
Jan 4, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,209
Try this:

Book1
ABCDEF
1
2
3StartEnd
4CAD112/1/20213/1/2022
5xxx212/2/20213/2/2022
6CADxxx312/3/20213/3/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))
 

mdmhal

New Member
Joined
Jan 4, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Eric,

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

Thank you, thank you, thank you!
Hal
 

mdmhal

New Member
Joined
Jan 4, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 4, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 4, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,209
I'm going to need an example. The one I created works:

Book1
ABCDEF
1
2
3StartEnd
4CAD11/12/20221/14/2022
5xxx212/2/20213/2/2022
6CADxxx312/3/20213/3/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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,627
Members
430,372
Latest member
contentment

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top