SUMPRODUCT with TEXT and dates
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 |
SUMPRODUCT with TEXT and dates
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 |
SUMPRODUCT with TEXT and dates
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 |
SUMPRODUCT with TEXT and dates
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 "Joe Gieder" wrote: 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 |
SUMPRODUCT with TEXT and dates
Do you want to send me a copy of your w/sheet?
toppers at REMOVETHIS johntopley.fsnet.co.uk "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 |
All times are GMT +1. The time now is 11:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com