sum over a date range

L

Lynn

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
 
T

T. Valko

I'm not a big fan of using these types of date expressions:
=--"01-Apr-09"
Click to expand...
<=--"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 said:
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
Click to expand...
 
S

Shane Devenshire

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 said:
I'm not a big fan of using these types of date expressions:
=--"01-Apr-09"
Click to expand...
<=--"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 said:
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
Click to expand...
Click to expand...
 
B

Bernard Liengme

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
 
S

Shane Devenshire

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.
 
T

T. Valko

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 said:
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 said:
I'm not a big fan of using these types of date expressions:
=--"01-Apr-09"
Click to expand...
<=--"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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
L

Lynn

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 said:
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 said:
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
Click to expand...
Click to expand...
 
L

Lynn

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hlookup problem in knowing what range to use 4
SumIF referencing to a CELL, rather than text.... 1
How to total itmes if they fall between a date range 1
When building a chart Dates are been included that are not in thedate range 1
Modifying a complex date formual to count months 4
counting items within a date range 1
how to add subtotals to a report? 2
RANDBETWEEN problems 7

Top