# 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:

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"
<=--"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:

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

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"
<=--"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:

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

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+D16)

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"
<=--"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:

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

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:

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

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:

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: