Formula to pick a date between 2 selected dates in order to add numbers together.

[Quiltface]

I am trying to create a formula that will basically look at a date that i have in a cell determine wether or not it is in between 2 other dates that i have in 2 other cells and if so add a number that i have in a different cell. If none of the dates are in between the 2 dates I want it to display message.

Ok so that might be very confusing so Im going to give you an example...
A B
1] 10/10/2009 $500
2] 10/20/2009 $600
3] 10/23/2009 $400
4]
5] 10/15/2009 10/25/2009
6]
7] Desired output here in A7


In this case It should say $1000 but i want it to display a message saying like No Data or something if there wasnt anything in that date window i created.

Any help will be appreciated.

O and im using office 2007

[DonkeyOte]

Given your use of XL2007 I would be inclined to use

A7: =SUMIFS($B$1:$B$4,$A$1:$A$4,">="&$A$5,$A$1:$A$4,"<="&$B$5)

Then apply a custom format to cell A7 (ie via CTRL + 1) of: #,##0.00;[red](#,##0.00);"No Data"
(alter number formats to suit)

The above format: positive; negative format; zero format will ensure that 0 is displayed and printed as No Data but the underlying value persists as 0.

[Quiltface]

You da man.

Is there a way this could work with 2003?

[DonkeyOte]

Pre2007, SUMPRODUCT is generally the adopted approach

=SUMPRODUCT(--($A$1:$A$4>=$A$5),--($A$1:$A$4<=$B$5),$B$1:$B$4)

this will work in 2007 also but if you're running exclusively in 2007 environment the SUMIFS approach would be deemed preferable in terms of efficiency etc...

[Quiltface]

Very nice, thank you very much.