Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > sum over a date range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 131
Default sum over a date range
Row†“ / Column†’ O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default sum over a date range
I'm not a big fan of using these types of date expressions:

=--"01-Apr-09"
<=--"30-Jun-09"

Try it like this (I'm leaving out all the file/sheet names):

=SUMPRODUCT(--($O$3:$O$8=DATE(2009,4,1)),--($O$3:$O$8<=DATE(2009,6,30)),$Z$3:$Z$8+$AC$3:$AC$8 +$AL$3:$AL$8)

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
Row? / Column? O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range
of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region'!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region'!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region'!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region'!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,346
Default sum over a date range
Hi,

For my education, what is the problem with the -- coersion of dates? And
why doesn't that apply to --(A1:A5=7)?


Cheers,
Shane Devenshire


"T. Valko" wrote:

I'm not a big fan of using these types of date expressions:

=--"01-Apr-09"
<=--"30-Jun-09"

Try it like this (I'm leaving out all the file/sheet names):

=SUMPRODUCT(--($O$3:$O$8=DATE(2009,4,1)),--($O$3:$O$8<=DATE(2009,6,30)),$Z$3:$Z$8+$AC$3:$AC$8 +$AL$3:$AL$8)

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
Row? / Column? O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range
of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region'!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region'!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region'!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region'!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default sum over a date range
For my education, what is the problem with the -- coersion of dates?

It's not necessarily a problem although it could be depending on your
regional settings seeing as how there are so many different date formats. To
me it's just too cryptic.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

For my education, what is the problem with the -- coersion of dates? And
why doesn't that apply to --(A1:A5=7)?


Cheers,
Shane Devenshire


"T. Valko" wrote:

I'm not a big fan of using these types of date expressions:

=--"01-Apr-09"
<=--"30-Jun-09"

Try it like this (I'm leaving out all the file/sheet names):

=SUMPRODUCT(--($O$3:$O$8=DATE(2009,4,1)),--($O$3:$O$8<=DATE(2009,6,30)),$Z$3:$Z$8+$AC$3:$AC$8 +$AL$3:$AL$8)

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
Row? / Column? O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date
range
of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region'!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region'!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region'!$AC$3:$AC$8+'[ADR Statistics - template.xls]
Region'!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,104
Default sum over a date range
Put the dates to be tested into cells (K3 and L3)
Then use
=SUMPRODUCT(--(O3:O8=K3),--(O3:O8<=L3),(Z3:Z8+AC3:AC8+AI3:AI8))

If it references another book it will look like
=SUMPRODUCT(--([MonthList.xlsx]Sheet3!O3:O8=A1),--([MonthList.xlsx]Sheet3!O3:O8<=B1,([MonthList.xlsx]Sheet3!Z3:Z8+[MonthList.xlsx]Sheet3!AC3:AC8+[MonthList.xlsx]Sheet3!AI3:AI8))
Where A1 and B1 hold the dates

I try never to 'hardcode' dates, but if I have to then I use this method:
=SUMPRODUCT(--([MonthList.xlsx]Sheet3!O3:O8=Date(2009,4,1)...... rather
than any specific date format

best wishes

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lynn" wrote in message
...
Row? / Column? O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range
of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region'!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region'!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region'!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region'!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 131
Default sum over a date range
Thank you all for replying. I still did not get what I was expecting.
Do I have to change my column 'O' dates?
I'll play around with this when I have more time and will calculate manually
for now but I appreciate the help.
Lynn

"Bernard Liengme" wrote:

Put the dates to be tested into cells (K3 and L3)
Then use
=SUMPRODUCT(--(O3:O8=K3),--(O3:O8<=L3),(Z3:Z8+AC3:AC8+AI3:AI8))

If it references another book it will look like
=SUMPRODUCT(--([MonthList.xlsx]Sheet3!O3:O8=A1),--([MonthList.xlsx]Sheet3!O3:O8<=B1,([MonthList.xlsx]Sheet3!Z3:Z8+[MonthList.xlsx]Sheet3!AC3:AC8+[MonthList.xlsx]Sheet3!AI3:AI8))
Where A1 and B1 hold the dates

I try never to 'hardcode' dates, but if I have to then I use this method:
=SUMPRODUCT(--([MonthList.xlsx]Sheet3!O3:O8=Date(2009,4,1)...... rather
than any specific date format

best wishes

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lynn" wrote in message
...
Row? / Column? O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range
of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region'!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region'!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region'!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region'!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 131
Default sum over a date range
Row†“ / Column†’ O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

Row†“ / Column†’ M N
Q1 01-Apr-09 30-Jun-09
Q2 01-Jul-09 30-Sep-09
Q3 01-Oct-09 31-Dec-09
Q4 01-Jan-10 31-Mar-10

I am trying to add columns Z+AC+AL if column O falls within a date range of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation now is:

=SUMPRODUCT(--'[ADR Statistics -
template.xls]Region!$O$3:$O$31=M1)--('[ADR Statistics - template.xls]
Region!$O$3:$O$31<=N1)*('[ADR Statistics - template.xls]
Region!$Z$3:$Z$31+'[ADR Statistics - template.xls]
Region!$AC$3:$AC$31+'[ADR Statistics - template.xls] Region!$AL$3:$AL$31)

It is returning an answer of €˜60, ignoring the Apr 1, 2009 date.
(To make this sort of work, I removed a couple of "," and added an * to your
solution.)

Lynn



"Bernard Liengme" wrote:

Put the dates to be tested into cells (K3 and L3)
Then use
=SUMPRODUCT(--(O3:O8=K3),--(O3:O8<=L3),(Z3:Z8+AC3:AC8+AI3:AI8))

If it references another book it will look like
=SUMPRODUCT(--([MonthList.xlsx]Sheet3!O3:O8=A1),--([MonthList.xlsx]Sheet3!O3:O8<=B1,([MonthList.xlsx]Sheet3!Z3:Z8+[MonthList.xlsx]Sheet3!AC3:AC8+[MonthList.xlsx]Sheet3!AI3:AI8))
Where A1 and B1 hold the dates

I try never to 'hardcode' dates, but if I have to then I use this method:
=SUMPRODUCT(--([MonthList.xlsx]Sheet3!O3:O8=Date(2009,4,1)...... rather
than any specific date format

best wishes

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lynn" wrote in message
...
Row? / Column? O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range
of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region'!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region'!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region'!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region'!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn




Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,346
Default sum over a date range
Hi,

You can use

=SUMPRODUCT((A1:A6=C1)*(A1:A6<=D1),B1:B6+C1:C6+D1 :D6)

where C1 and D1 contain the upper an lower dates. I have not adjusted the
ranges for your sample data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Lynn" wrote:

Row†“ / Column†’ O Z AC AL
3 03-Jan-09 10.00 10.00 10.00
4 04-May-09 20.00 20.00 20.00
5 07-Mar-09 30.00 30.00 30.00
6 07-Jul-09 40.00 40.00 40.00
7 03-Jun-08 50.00 50.00 50.00
8 01-Apr-09 60.00 60.00 60.00

I am trying to add columns Z+AC+AL if column O falls within a date range of
01-Apr-09 to 30-Jun-09. I am transferring it to another worksheet. My
equation is:

=SUMPRODUCT((--'[ADR Statistics -
template.xls]Region'!$O$3:$O$8=--"01-Apr-09")--('[ADR Statistics -
template.xls] Region!$O$3:$O$8<=--"30-Jun-09")*('[ADR Statistics -
template.xls] Region!$Z$3:$Z$8+'[ADR Statistics - template.xls]
Region!$AC$3:$AC$8+'[ADR Statistics - template.xls] Region!$AL$3:$AL$8))

but it is returning a different number than expected.
Thanks, Lynn

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
Sum If range of dates date range, sum totals Stilmovin Excel Worksheet Functions 7 December 16th 08 05:49 PM
Create a formula in a date range to locate a specific date - ecel util Excel Discussion (Misc queries) 0 February 19th 07 03:03 PM
Formula for determining if two date columns fall within specific date range Igottabeme Excel Worksheet Functions 2 April 21st 06 02:50 AM
Formula for determining if two date columns fall within specific date range Igottabeme Excel Discussion (Misc queries) 1 April 20th 06 10:03 PM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM


All times are GMT +1. The time now is 12:32 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement