Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add value if date is in an interval... tricky?!
This may sound easy, and I thought it would be easy, but after som thought I
realize that what I'm trying to do probably requires a bit of hard-thinking. I have got a table that I exported from my database (Access) and it has 3 columns (call this list1) 1. Date1 2. Date2 3. A value Now, I have created another column with dates (in chronological order), ranging from 2005-01-01 to 2007-10-31 (call this list2) and I want - for each of this dates - to have a SUM of all values THAT have the date in their interval (interval between Date1 and Date2). So, for my first date in list2: "2005-01-01" I want to have a total sum of all values in list1 where "2005-01-01" is between Date1 and Date2. And so on for rest of the dates in list2 (2005-01-02 -- 2007-10-31). (I really need this for a much promised and anticipated report analyzing our sales, so all help is MUCH appreciated) Cheers, Mikael (This is cross-posted in Excel functions) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add value if date is in an interval... tricky?!
=SUMPRODUCT(--(list2date=list1date1),--(list2date<=list1date2),list1value)
where list1date1 is the range of date1's etc. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mikael Lindqvist" wrote in message ... This may sound easy, and I thought it would be easy, but after som thought I realize that what I'm trying to do probably requires a bit of hard-thinking. I have got a table that I exported from my database (Access) and it has 3 columns (call this list1) 1. Date1 2. Date2 3. A value Now, I have created another column with dates (in chronological order), ranging from 2005-01-01 to 2007-10-31 (call this list2) and I want - for each of this dates - to have a SUM of all values THAT have the date in their interval (interval between Date1 and Date2). So, for my first date in list2: "2005-01-01" I want to have a total sum of all values in list1 where "2005-01-01" is between Date1 and Date2. And so on for rest of the dates in list2 (2005-01-02 -- 2007-10-31). (I really need this for a much promised and anticipated report analyzing our sales, so all help is MUCH appreciated) Cheers, Mikael (This is cross-posted in Excel functions) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum.if date is in an interval... | Excel Worksheet Functions | |||
lookup date in the interval | Excel Programming | |||
Date Interval Predictions | Excel Discussion (Misc queries) | |||
Date Interval Predictions | Excel Worksheet Functions | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) |