I made up a List1 in A1:C10, and a list of dates for List2 in F1:F5
In G1 I used the formula
=SUMPRODUCT(--($A$1:$A$10<=F1),--($B$1:$B$10=F1),$C$1:$C$10)
I copied this down to G5
Not tested with much data but seems OK
You lists will be longer so use formula in the form
=SUMPRODUCT(--($A$1:$A$3000<=F1),.....
It CANNOT be used with full-columns =SUMPRODUCT(--(A:A<=F1)......
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"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 programming)