List of dates
 

Hi,

I have a list of Dates in Column A and a list of Rents amounts in Column B.
I'm trying to create a formula that will provide a rent for a specific month.
Here's an example:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150


i need a formula which will pull the monthly rent amount from 6/1/2006 thru
9/1/2009. I've tried a few arrays and sumproducts, but my formula's will only
see if the 5/1/2003 is greater than 6/1/2006 and since it's not i get an
error or it will only pull the $1400. Any help would be greatly appreciated.


Thanks!

Dave

List of dates
 

Do you need the total or to display the amount of rent of a particular date?
The latter can be done using LOOKUP. Bout the latter, SUMIF(high date value)
less SUMIF(low date value) would be the simplest.

David wrote:
Hi,

I have a list of Dates in Column A and a list of Rents amounts in Column B.
I'm trying to create a formula that will provide a rent for a specific month.
Here's an example:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150

i need a formula which will pull the monthly rent amount from 6/1/2006 thru
9/1/2009. I've tried a few arrays and sumproducts, but my formula's will only
see if the 5/1/2003 is greater than 6/1/2006 and since it's not i get an
error or it will only pull the $1400. Any help would be greatly appreciated.


Thanks!

Dave

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200909/1

List of dates
 

I mean "Bout the FORMER".

pogiman wrote:
Do you need the total or to display the amount of rent of a particular date?
The latter can be done using LOOKUP. Bout the latter, SUMIF(high date value)
less SUMIF(low date value) would be the simplest.

Hi,

[quoted text clipped - 15 lines]

Dave

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200909/1

List of dates
 

I don't need a sum. I need the rent for that specific month. The rent amounts
that i put in are monthly rents and the dates are when they were changed.

Thanks!

Dave

"pogiman via OfficeKB.com" wrote:

I mean "Bout the FORMER".

pogiman wrote:
Do you need the total or to display the amount of rent of a particular date?
The latter can be done using LOOKUP. Bout the latter, SUMIF(high date value)
less SUMIF(low date value) would be the simplest.

Hi,

[quoted text clipped - 15 lines]

Dave

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200909/1

List of dates
 

As long as your data is sorted in ascending order as is shown in your
sample:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150

With that data in the range A2:B4...

D2 = some date like 6/1/2006

=LOOKUP(D2,A$2:B$4)

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hi,

I have a list of Dates in Column A and a list of Rents amounts in Column
B.
I'm trying to create a formula that will provide a rent for a specific
month.
Here's an example:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150


i need a formula which will pull the monthly rent amount from 6/1/2006
thru
9/1/2009. I've tried a few arrays and sumproducts, but my formula's will
only
see if the 5/1/2003 is greater than 6/1/2006 and since it's not i get an
error or it will only pull the $1400. Any help would be greatly
appreciated.


Thanks!

Dave

List of dates
 

Perfect!

T. Valko wrote:
As long as your data is sorted in ascending order as is shown in your
sample:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150

With that data in the range A2:B4...

D2 = some date like 6/1/2006

=LOOKUP(D2,A$2:B$4)

Hi,

[quoted text clipped - 20 lines]

Dave

--
Message posted via http://www.officekb.com

List of dates
 

Excellent -- This answers my question! Thank you greatly!

"T. Valko" wrote:

As long as your data is sorted in ascending order as is shown in your
sample:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150

With that data in the range A2:B4...

D2 = some date like 6/1/2006

=LOOKUP(D2,A$2:B$4)

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hi,

I have a list of Dates in Column A and a list of Rents amounts in Column
B.
I'm trying to create a formula that will provide a rent for a specific
month.
Here's an example:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150


i need a formula which will pull the monthly rent amount from 6/1/2006
thru
9/1/2009. I've tried a few arrays and sumproducts, but my formula's will
only
see if the 5/1/2003 is greater than 6/1/2006 and since it's not i get an
error or it will only pull the $1400. Any help would be greatly
appreciated.


Thanks!

Dave

List of dates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Excellent -- This answers my question! Thank you greatly!

"T. Valko" wrote:

As long as your data is sorted in ascending order as is shown in your
sample:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150

With that data in the range A2:B4...

D2 = some date like 6/1/2006

=LOOKUP(D2,A$2:B$4)

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hi,

I have a list of Dates in Column A and a list of Rents amounts in
Column
B.
I'm trying to create a formula that will provide a rent for a specific
month.
Here's an example:

Date Rent
5/1/2003 1400
5/1/2007 1800
5/1/2009 2150


i need a formula which will pull the monthly rent amount from 6/1/2006
thru
9/1/2009. I've tried a few arrays and sumproducts, but my formula's
will
only
see if the 5/1/2003 is greater than 6/1/2006 and since it's not i get
an
error or it will only pull the $1400. Any help would be greatly
appreciated.


Thanks!

Dave