adding up money from the right week

[NemoRabbit]

I need to be able to sum an amount of money if it has been collected in the right week. i.e

Data

Date Collected Amount
01/07/2008 £108.56
04/07/2008 £10.15
08/07/2008 £48.27

Table showing results

Date from Date to Amount
01/07/2008 07/07/2008 £118.71

Thanks

 

[Jonmo1]

=SUMPRODUCT(--(DateRange >= FromDate),--(DateRange <= ToDate),AmountRange)

IMPORTANT NOTES
Ranges cannot be entire column refs like A:A, you must specify row #s like A1:A100
Ranges must all be same size

Hope that helps..

 

[NemoRabbit]

I've got

=SUMPRODUCT(--('NUD Incoming'!D1:D1000 >= 12/7/2008),--('NUD Incoming'!D1:D100 <= 6/7/2008),'NUD Incoming'!F1:F1000)

but it doesn't work! am i missing the point?

 

[Jonmo1]

Just about right, do 1 of 2 things

change the dates like
12/7/2008
to
"12/7/2008"*1 (Including the quotes)

Or put the dates in a cell and refer to the cell in the formula..

Either
=SUMPRODUCT(--('NUD Incoming'!D1:D1000 >= "12/7/2008"*1),--('NUD Incoming'!D1:D100 <= "6/7/2008"*1),'NUD Incoming'!F1:F1000)

Or
=SUMPRODUCT(--('NUD Incoming'!D1:D1000 >= A1),--('NUD Incoming'!D1:D100 <= B1),'NUD Incoming'!F1:F1000)
A1 = 12/7/2008
B1 = 6/7/2008

 

[NemoRabbit]

nope, neither of those work either!

sorry!

 

[Jonmo1]

Describe "Doesn't Work"

Do you get error? - What error?
Do you get wrong result? What result DID it give, what result did you expect?

Show some sample data with the HTML maker
http://www.mrexcel.com/forum/showthread.php?t=89356

Test that your dates are actually dates
=ISNUMBER('NUD Incoming'!D1) - is that TRUE or FALSE
Same for the Values you want to add
=ISNUMBER('NUD Incoming'!F1) - is that TRUE or FALSE