Dynamic range for date input

[JohannesM]

Hi,

I have a report were the user can choose the range by writing the "from week" in one cell and "to week" in another. The only solution I can produce is to take one sumifs for every possible week using offset, but there must be some smarter way of doing this?

This is how part of my dumb formula looks like at the moment and it only covers 3 weeks.
SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;$C$140)+SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;OFFSET($C$140;-1;0))+SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;OFFSET($C$140;-2;0))

Is there a way to get a condition in one single sumifs that says that it should sum if the week is a part of a range as mentioned above?

Tanks in advance!
/Johannes

 

[gaz_chops]

Can you post a sample of your data?

 

[GlennUK]

If column G really contains dates, then, using 2 SUMIFS together will cover any range. The logic is:
=SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;">="&start_date)-SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;">"&end_date)

 

[JohannesM]

If column G really contains dates, then, using 2 SUMIFS together will cover any range. The logic is:
=SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;">="&start_date)-SUMIFS(Volume!$J:$J;Volume!$B:$B;"Out";Volume!$E:$E;H153;Volume!$G:$G;">"&end_date)

Wow, thanks that was really smart =)
Actually tried to use => but didn´t work since I didn´t know that I had to use the "".
Thanks Glenn and gaz!

Mvh
Johannes