Find a total that falls between a date range

[CreativeRova]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]1/1/14
[/TD]
[TD]7/1/14
[/TD]
[TD]14/1/14
[/TD]
[TD]21/1/14
[/TD]
[TD]28/1/14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Today
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]13/12/13
[/TD]
[TD][/TD]
[TD]Sold
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bonus
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]???????
[/TD]
[TD][/TD]
[TD]Total
[/TD]
[TD]8
[/TD]
[TD]3
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Guys and Girls,

I am after a formula for the above sample table. In the total window with the question marks i need to show the total when todays date falls between the date ranges shown on the right. So as the date moves from 6/1/14 to 7/1/14 my total will go from 8 to 3.

Essentially i know the working but not the formula.
Equal to or greater than current date less than next date show total. If not move to next cell check again. I can do this at the moment but it requires 52 if commands for each week i would like to avoid that if possible. Also i have tried pivot tables and that doesn't seem to work as i will require the result to be used in another table. So Avoiding pivot tables would also be preferred.

Thanks

 

[Drrellik]

=sumifs(e5:i5,e1:i1,"= < b3",e1:i1,"= > b3")

 

[CreativeRova]

Thanks Drrellik, there seems to be something missing though. As i did an exact copy of the above table and the formula always produces the answer "0" doesn't matter what date.

 

[Aladin Akyurek]

What is the value you want to see in B5, given the date in B3 and this sample of data in D:I?

 

[CreativeRova]

What I want to see in B5 is the total. So if today was 6/1/14 B5 should show E5 which is "8" if today was 22/1/14 B5 should show H5 which is "12" As this is the total for any date from 21/1/14 - 27/1/14.

 

[Aladin Akyurek]

What I want to see in B5 is the total. So if today was 6/1/14 B5 should show E5 which is "8" if today was 22/1/14 B5 should show H5 which is "12" As this is the total for any date from 21/1/14 - 27/1/14.

B5:

=LOOKUP(B3,$E$1:$I$1,$E$5:$I$5)

Note. It's better to write down the dates as 16-Dec-13 though instead of: 5/12/13 or 12/5/13 to avoid any confusion.

 

[CreativeRova]

Thank you so much Aladin. That works perfectly. Also that's a very good point about the dates.

 

[Aladin Akyurek]

Thank you so much Aladin. That works perfectly. Also that's a very good point about the dates.

You are welcome. Thanks for providing feedback.