| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT with TEXT and dates | Excel Worksheet Functions | |||
| Sumproduct using dates | Excel Worksheet Functions | |||
| Receive #VALUE! when I mix text with dates using SUMPRODUCT | Excel Worksheet Functions | |||
| SUMPRODUCT ON DATES | Excel Worksheet Functions | |||
| sumproduct between dates | Excel Worksheet Functions | |||
Linear Mode
