SUMPRODUCT and Dates
 

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

SUMPRODUCT and Dates
 

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

--
Don Guillett
SalesAid Software

"Joe Gieder" wrote in message
...
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

SUMPRODUCT and Dates
 

On Jun 6, 6:30 pm, Joe Gieder
wrote:
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