SUMPRODUCT with TEXT and dates

[Guest]

First, sorry for the long post and thank you for helping and looking.

I'm trying to use this formula:
SUMPRODUCT(--(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5000,4)=$A26),(YEAR(DATEVALUE('[Spares Quotes List
V22 26Apr2007.xls]All'!$M$2:$M$5000)=1900)))

and it returns value, the comment says "a value used in the formula is of
the wrong data type". There ar etwo types of data $A$2:$A$5000 is text and
$M$2:$M$5000 is a date. I want to count how many times the year 1900 shows up
for a particular proposal number ($A$2:$A$5000). I use a value of 0 in the
date field when I don't need to concerned with the information but I need to
count it so the totals work out.

Can this be done?
Thank you in advance for the help.
Joe

 

[T. Valko]

DATEVALUE will return an error if the cells contain true Excel dates.
DATEVALUE is for use on TEXT representations of dates. If the cells in
question contain 0s just test for 0:

SUMPRODUCT(--(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5000,4)=$A26),--(ISNUMBER('[Spares Quotes List
V22 26Apr2007.xls]All'!$M$2:$M$5000)),--('[Spares Quotes List
V22 26Apr2007.xls]All'!$M$2:$M$5000=0))

Biff