I need a formula that calculates the amount ($) in one column based on the
date that coincides with the amount (another column) and also 30 days less
than the spreadsheets' date. How would I begin to write that? Help... and
thanks!

=SUMPRODUCT(--(TODAY()-A2:A300<31),B2:B300)
where column A holds your dates and column B holds the amount and by
"spreadsheets' date" you men todays date

"KJo" wrote in message
...
I need a formula that calculates the amount ($) in one column based on the
date that coincides with the amount (another column) and also 30 days less
than the spreadsheets' date. How would I begin to write that? Help... and
thanks!

=INDEX(A:A,MATCH(today()-30,B:B,0))

--
HTH

Bob Phillips

"KJo" wrote in message
...
I need a formula that calculates the amount ($) in one column based on the
date that coincides with the amount (another column) and also 30 days less
than the spreadsheets' date. How would I begin to write that? Help... and
thanks!

Hi
Try something like this:
=SUMPRODUCT((A2:A1000<=G1)*(A2:A1000G1-30)*(B2:B1000))
assuming your date is in G1, your list of dates is A2:A1000 and your list of
values is B2:B1000
Hope this helps.
For more informtion on how SUMPRODUCT works, have a look he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Andy.


"KJo" wrote in message
...
I need a formula that calculates the amount ($) in one column based on the
date that coincides with the amount (another column) and also 30 days less
than the spreadsheets' date. How would I begin to write that? Help... and
thanks!

I think this is exactly what I need as I wouldn't be using a 'today' date.
I'll give this a try. Thank You!

"Andy B" wrote:

Hi
Try something like this:
=SUMPRODUCT((A2:A1000<=G1)*(A2:A1000G1-30)*(B2:B1000))
assuming your date is in G1, your list of dates is A2:A1000 and your list of
values is B2:B1000
Hope this helps.
For more informtion on how SUMPRODUCT works, have a look he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Andy.


"KJo" wrote in message
...
I need a formula that calculates the amount ($) in one column based on the
date that coincides with the amount (another column) and also 30 days less
than the spreadsheets' date. How would I begin to write that? Help... and
thanks!