Find a total that falls between a date range

[CreativeRova]

			Date	1/1/14	7/1/14	14/1/14	21/1/14	28/1/14
	Today
Date	13/12/13		Sold	6	2	8	9	10
			Bonus	2	1	3	3	3
Total	???????		Total	8	3	11	12	13

<tbody>
</tbody>

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.