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)