Sumproduct w/date criteria not working

[JANA]

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)

[Max]

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 <at>yahoo<dot>com
----
"JANA" <[email protected]> wrote in message
news:[email protected]...
> 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)

[JulieD]

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" <[email protected]> wrote in message
news:[email protected]...
> 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)

[Bernard Liengme]

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" <[email protected]> wrote in message
news:[email protected]...
> 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)

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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)

[Max]

Nice ! "--" is 9 keystrokes less than DATEVALUE() ! <g>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Bob Phillips]

Exactly, important when RSI kicks in <G>

Bob

"Max" <[email protected]> wrote in message
news:[email protected]...
> Nice ! "--" is 9 keystrokes less than DATEVALUE() ! <g>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

"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 <at>yahoo<dot>com
----