Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 364
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying records from a date interval from one worksheet to another ricky[_2_] Excel Discussion (Misc queries) 1 July 22nd 07 05:32 AM
Date Interval Predictions [email protected] Excel Discussion (Misc queries) 1 October 31st 06 01:19 PM
Date Interval Predictions [email protected] Excel Worksheet Functions 1 October 31st 06 12:48 PM
Calculations with date time interval Todd F. Excel Worksheet Functions 6 September 22nd 05 07:43 PM
convert interval to various separate date , time, hr, minutes Todd F. Excel Worksheet Functions 4 July 12th 05 07:25 PM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"