SUMPRODUCT with TEXT and dates

[Joe Gieder]

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

I'm have this formula but it doesn’t 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 I’m
looking for is 7. I’m 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

 

[Toppers]

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]

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

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))

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

I'm have this formula but it doesn’t 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 I’m
looking for is 7. I’m 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

 

[Joe Gieder]

The strange thig is if I use just the =SUMPRODUCT(YEAR('[Spares Quotes List
V22
26Apr2007.xls]All'!$M$2:$M$5000)=1900) I received the same error message.

Joe

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

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))

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

I'm have this formula but it doesn’t 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 I’m
looking for is 7. I’m 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

 

[Toppers]

Do you want to send me a copy of your w/sheet?

toppers at REMOVETHIS johntopley.fsnet.co.uk