| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I generate a list of random dates from dates I specify | Excel Worksheet Functions | |||
| How to filter out all the partial dates from a big list of dates? | Excel Discussion (Misc queries) | |||
| Counting dates, within a list of dates | Excel Worksheet Functions | |||
| Need to pull current dates from list w/many dates | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Hybrid Mode
