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