Thank you for the suggestion. I tried what you have and I get a value error
the comment says "a value used in the formula is of the wrong data type". I
have looked at everything and don't see wrong data types.

Thanks
Joe

"Toppers" wrote:

TRY:


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


"Joe Gieder" wrote:

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

I'm have this formula but it doesnt work:
SUMPRODUCT(--(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5000,4)=$A26*(YEAR('[Spares Quotes List V22
26Apr2007.xls]All'!$M$2:$M$5000=1900)))

The result is 38000, not correct. If I take out the LEFT function I get
20900, not correct either (I need LEFT). There are 20 cells that match A26
and there are 7 that match 1900 (actual date is 1/0/1900), the result Im
looking for is 7. Im using two types of data, $A$2:$A$5000 is text and
$M$2:$M$5000 is a date, as my criteria. The reason I use 1/0/1900 is I enter
0 in the date field and this is what I get. I don't need to be concerned with
the information in these rows but I need to maintain integrity and I need to
count them so the totals work out. I have tried MONTH with a value of 1 and
get 20 and I have tried DAY with a value of 0 and get 19.

Can this be done?

Thank you in advance for the help.
Joe