SUMIFS - Date Range w/ Formula Driven Date

[smas_tx]

Hi, decided I've spent enough time fumbling with this, time to ask for help.

The RED section or 'criteria range 2' of this sumifs formula is what I need some help on.

Column H = Duration in weeks, $H$6:$H$104
Column F = Start Date Range, $F$6:$F$104

Hypothetical 'End Date' column would equal -> F6+((H6*7)+1))
- Start date + ((7 days * # of weeks) + 1 day)
- Trying to avoid entering this column

=SUMIFS($J$6:$J$104,$F$6:$F$104,"<=" & T5,($F$6:$F$104+(($H$6:$H$104*7)+1)),">=" & T5)

Thanks in advance.

 

[Jonmo1]

I would recommand using a helper column to enter that hypothetical end date
It seems like information usefull for other purposes in your sheet besides this one formula

Say column I

I6 and filled down
=F6+H6*7+1

Then use
=SUMIFS($J$6:$J$104,$F$6:$F$104,"<=" & T5,$I$6:$I$104,">=" & T5)

 

[smas_tx]

Thanks for the reply Jonmo1 - your suggestion is how I have it set up now and I agree, but in an effort to reduce columns, I've been asked if I can embed the end date calc in the formula - currently have 4 activities with start/end dates + other data, so trying to cut down the current 8 date columns... struggling with doing so

...and no they don't want to just hide them - tried that

 

[Jonmo1]

Gotta love upper management.
Appearance is more important than function.

SUMIFS can't do it. It can only evaluate values in the cells exactly as they exist. No manipulation of those values allowed.

You'll need the much less efficient sumproduct version

=SUMPRODUCT(--($F$6:$F$104<=T5),--(($F$6:$F$104+$H$6:$H$104*7+1)>=T5),$J$6:$J$104)



Back to the point of helper column.
I understand the need for 'neatness'
But that should never outweigh the overal performance of the sheet.

If that hypothetical end date is needed for more than 1 formula, then it's duplicating calculations over and over again, that only needs to be done once.

The helper column can be 'Hidden'
It can be moved far to the side of the useable portion of the sheet (put it in column ZZ or whatever)
It can even be put on a different sheet, and hidden.

 

[smas_tx]

True, might just move/hide them outside the usable area as you suggested, probably wouldn't even know.

Appreciate your time/help. Take it easy.

 

[Jonmo1]

You're welcome.