adding up money from the right week

NemoRabbit

Board Regular
Joined
Sep 28, 2005
Messages
64
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=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

Board Regular
Joined
Sep 28, 2005
Messages
64
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top