|
Sumproduct w/date criteria not working
I'm using a sumproduct formula because I have 2 (or sometimes more) criteria
to meet. This has worked in the past for me, but this time one of the criteria is a date field and my formula isn't working. Below is an example and the formulas I'm using. Please correct my formulas or tell me a different formula to use to make this work. Thanks! Spreadsheet 1, tab A A B C 1 319 11/1/2004 $10 2 357 11/1/2004 $15 3 319 12/1/2004 $20 4 357 12/1/2004 $25 5 319 1/1/2005 $30 6 357 1/1/2005 $35 7 319 2/1/2005 $40 8 357 2/1/2005 $45 Spreadsheet 2, tab A A B 1 319 $30 (formula below giving me $100 - totaling all 319) 2 319 $70 (formula below giving me 0) cell B1 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet 1.xls]A'!$b$1:$b$7<"1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) cell B2 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A2),--('[spreadsheet 1.xls]A'!$b$1:$b$7="1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) |
Instead of:
<"1/1/2005" ="1/1/2005" Try using DATEVALUE(...), viz.: <DATEVALUE("1/1/2005") =DATEVALUE("1/1/2005") as in: ... A!$B$1:$B$7<DATEVALUE("1/1/2005") ... A!$B$1:$B$7=DATEVALUE("1/1/2005") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "JANA" wrote in message ... I'm using a sumproduct formula because I have 2 (or sometimes more) criteria to meet. This has worked in the past for me, but this time one of the criteria is a date field and my formula isn't working. Below is an example and the formulas I'm using. Please correct my formulas or tell me a different formula to use to make this work. Thanks! Spreadsheet 1, tab A A B C 1 319 11/1/2004 $10 2 357 11/1/2004 $15 3 319 12/1/2004 $20 4 357 12/1/2004 $25 5 319 1/1/2005 $30 6 357 1/1/2005 $35 7 319 2/1/2005 $40 8 357 2/1/2005 $45 Spreadsheet 2, tab A A B 1 319 $30 (formula below giving me $100 - totaling all 319) 2 319 $70 (formula below giving me 0) cell B1 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet 1.xls]A'!$b$1:$b$7<"1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) cell B2 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A2),--('[spreadsheet 1.xls]A'!$b$1:$b$7="1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) |
Hi Jana
you can't directly reference a date using "mm/dd/yy" because dates are stored in excel as a serial number not as a text string (which is what the " " indicate). You need to use the date within a DATEVALUE() to convert it, so your formulas would then be: cell B1 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet 1.xls]A'!$b$1:$b$7<datevalue("1/1/2005")),'[spreadsheet 1.xls]A'!$c$1:$c$7) cell B2 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A2),--('[spreadsheet 1.xls]A'!$b$1:$b$7=datevalue("1/1/2005")),'[spreadsheet 1.xls]A'!$c$1:$c$7) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "JANA" wrote in message ... I'm using a sumproduct formula because I have 2 (or sometimes more) criteria to meet. This has worked in the past for me, but this time one of the criteria is a date field and my formula isn't working. Below is an example and the formulas I'm using. Please correct my formulas or tell me a different formula to use to make this work. Thanks! Spreadsheet 1, tab A A B C 1 319 11/1/2004 $10 2 357 11/1/2004 $15 3 319 12/1/2004 $20 4 357 12/1/2004 $25 5 319 1/1/2005 $30 6 357 1/1/2005 $35 7 319 2/1/2005 $40 8 357 2/1/2005 $45 Spreadsheet 2, tab A A B 1 319 $30 (formula below giving me $100 - totaling all 319) 2 319 $70 (formula below giving me 0) cell B1 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet 1.xls]A'!$b$1:$b$7<"1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) cell B2 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A2),--('[spreadsheet 1.xls]A'!$b$1:$b$7="1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) |
Firstly, when trying to debug a problem: make it simple.
I tested your work on one sheet in a single workbook. Using The Formula Evaluation tool (I use Excel 2003), I found that the second array --($b$1:$b$7<"1/1/2005"), evaluated to (TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE) So that is where the problem lies. Next I tried =SUMPRODUCT(--(A1:A8=A1), --(B1:B8<DATE(2005,1,1)), C1:C8) and got the required answer of 30. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JANA" wrote in message ... I'm using a sumproduct formula because I have 2 (or sometimes more) criteria to meet. This has worked in the past for me, but this time one of the criteria is a date field and my formula isn't working. Below is an example and the formulas I'm using. Please correct my formulas or tell me a different formula to use to make this work. Thanks! Spreadsheet 1, tab A A B C 1 319 11/1/2004 $10 2 357 11/1/2004 $15 3 319 12/1/2004 $20 4 357 12/1/2004 $25 5 319 1/1/2005 $30 6 357 1/1/2005 $35 7 319 2/1/2005 $40 8 357 2/1/2005 $45 Spreadsheet 2, tab A A B 1 319 $30 (formula below giving me $100 - totaling all 319) 2 319 $70 (formula below giving me 0) cell B1 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet 1.xls]A'!$b$1:$b$7<"1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) cell B2 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A2),--('[spreadsheet 1.xls]A'!$b$1:$b$7="1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) |
My preferred way is to coerce the date as well as the True/False
sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet1.xls]A'!$b$1:$b$7<--"2005-01-01"),' [spreadsheet 1.xls]A'!$c$1:$c$7) -- HTH RP (remove nothere from the email address if mailing direct) "JANA" wrote in message ... I'm using a sumproduct formula because I have 2 (or sometimes more) criteria to meet. This has worked in the past for me, but this time one of the criteria is a date field and my formula isn't working. Below is an example and the formulas I'm using. Please correct my formulas or tell me a different formula to use to make this work. Thanks! Spreadsheet 1, tab A A B C 1 319 11/1/2004 $10 2 357 11/1/2004 $15 3 319 12/1/2004 $20 4 357 12/1/2004 $25 5 319 1/1/2005 $30 6 357 1/1/2005 $35 7 319 2/1/2005 $40 8 357 2/1/2005 $45 Spreadsheet 2, tab A A B 1 319 $30 (formula below giving me $100 - totaling all 319) 2 319 $70 (formula below giving me 0) cell B1 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A1),--('[spreadsheet 1.xls]A'!$b$1:$b$7<"1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) cell B2 =sumproduct(--('[spreadsheet 1.xls]A'!$a$1:$a$7=$A2),--('[spreadsheet 1.xls]A'!$b$1:$b$7="1/1/2005"),'[spreadsheet 1.xls]A'!$c$1:$c$7) |
Nice ! "--" is 9 keystrokes less than DATEVALUE() ! <g
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Exactly, important when RSI kicks in <G
Bob "Max" wrote in message ... Nice ! "--" is 9 keystrokes less than DATEVALUE() ! <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
"Bob Phillips" wrote:
Exactly, important when RSI kicks in <G ROTFL ! Over here, think it's more "RESI" risk (E=Eye) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
| All times are GMT +1. The time now is 02:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com