Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
This works like a charm.
Now I just want to calcluate the number of occurances of a string in column D (e.g., the number of times "Apples, Oranges") are listed in the column. That is, same setting, but instead of summing numbers I need a similar function, but that is counting a particular string. Is that doable? Kindly, Mikael "Bernard Liengme" wrote: 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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
Instead of two criteria and one data, just use three criteria, such as
=SUMPRODUCT(--($A$1:$A$10<=F1),--($B$1:$B$10=F1),--($D$1:$D$10="Apples")) "Mikael Lindqvist" wrote in message ... This works like a charm. Now I just want to calcluate the number of occurances of a string in column D (e.g., the number of times "Apples, Oranges") are listed in the column. That is, same setting, but instead of summing numbers I need a similar function, but that is counting a particular string. Is that doable? Kindly, Mikael "Bernard Liengme" wrote: 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
Yes, that's what I tried first, but I tried to count the letters A, B, C and
D. And this was not working, maybe letters are reserved? Anyhow, thanks a lot for invaluable help! : Cheers, Mikael "Stephen" wrote: Instead of two criteria and one data, just use three criteria, such as =SUMPRODUCT(--($A$1:$A$10<=F1),--($B$1:$B$10=F1),--($D$1:$D$10="Apples")) "Mikael Lindqvist" wrote in message ... This works like a charm. Now I just want to calcluate the number of occurances of a string in column D (e.g., the number of times "Apples, Oranges") are listed in the column. That is, same setting, but instead of summing numbers I need a similar function, but that is counting a particular string. Is that doable? Kindly, Mikael "Bernard Liengme" wrote: 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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
This is really odd but I can't the formula to work for columns bigger than
approximately 5000 rows. Anyone else aware of this limit and how to get round it? Regards, Mikael "Mikael Lindqvist" wrote: Yes, that's what I tried first, but I tried to count the letters A, B, C and D. And this was not working, maybe letters are reserved? Anyhow, thanks a lot for invaluable help! : Cheers, Mikael "Stephen" wrote: Instead of two criteria and one data, just use three criteria, such as =SUMPRODUCT(--($A$1:$A$10<=F1),--($B$1:$B$10=F1),--($D$1:$D$10="Apples")) "Mikael Lindqvist" wrote in message ... This works like a charm. Now I just want to calcluate the number of occurances of a string in column D (e.g., the number of times "Apples, Oranges") are listed in the column. That is, same setting, but instead of summing numbers I need a similar function, but that is counting a particular string. Is that doable? Kindly, Mikael "Bernard Liengme" wrote: 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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum.if date is in an interval...
Duh, for some reason I had a "#missing!" value in my column and that appeared
in row 6780... so, please ignore my previous post! //Mikael "Mikael Lindqvist" wrote: Yes, that's what I tried first, but I tried to count the letters A, B, C and D. And this was not working, maybe letters are reserved? Anyhow, thanks a lot for invaluable help! : Cheers, Mikael "Stephen" wrote: Instead of two criteria and one data, just use three criteria, such as =SUMPRODUCT(--($A$1:$A$10<=F1),--($B$1:$B$10=F1),--($D$1:$D$10="Apples")) "Mikael Lindqvist" wrote in message ... This works like a charm. Now I just want to calcluate the number of occurances of a string in column D (e.g., the number of times "Apples, Oranges") are listed in the column. That is, same setting, but instead of summing numbers I need a similar function, but that is counting a particular string. Is that doable? Kindly, Mikael "Bernard Liengme" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying records from a date interval from one worksheet to another | Excel Discussion (Misc queries) | |||
Date Interval Predictions | Excel Discussion (Misc queries) | |||
Date Interval Predictions | Excel Worksheet Functions | |||
Calculations with date time interval | Excel Worksheet Functions | |||
convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions |