|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com