SUMPRODUCT and Dates

[Guest]

First, Thank you for helping / looking.
I'm trying to count the number of cells that match two criteria and have
looked through many many responses and tried several of the suggestions but
no luck.
I have this formula:
SUMPRODUCT(--('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5000=$A26),--(YEAR(DATEVALUE('[Spares Quotes List
V22 26Apr2007.xls]All'!$M$2:$M$5000))=1900))

What it is $A$2:$A$5000=$A26 is text and $M$2:$M$5000))=1900 is a date.
1900 needs to be there because I place a 0 were I don't need to look at this
cell but I need to count it so the totals work out. Can I count using text
for one criteria and a date for the other?

Thanks in advance for your help.
Joe

 

[Don Guillett]

If col M has valid dates try just using
year(rnge)=1900

 

[vezerid]

First, Thank you for helping / looking.
I'm trying to count the number of cells that match two criteria and have
looked through many many responses and tried several of the suggestions but
no luck.
I have this formula:
SUMPRODUCT(--('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5000=$A26),--(YEAR(DATEVALUE('[Spares Quotes List
V22 26Apr2007.xls]All'!$M$2:$M$5000))=1900))

What it is $A$2:$A$5000=$A26 is text and $M$2:$M$5000))=1900 is a date.
1900 needs to be there because I place a 0 were I don't need to look at this
cell but I need to count it so the totals work out. Can I count using text
for one criteria and a date for the other?

Thanks in advance for your help.
Joe

Joe,

replace the YEAR(DATEVALUE(...))=1900 with M2:M5000=0?

DATEVALUE expects text and turns it into a date. Your text seems a bit
unorthodox, but I don't know what values you have in M2:M5000. If they
are dates then test for =0 is enough. If they are text, how do you
write it?

HTH
Kostis Vezerides