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 trying to use this formula: SUMPRODUCT(--(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5000,4)=$A26),(YEAR(DATEVALUE('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000)=1900))) and it returns value, the comment says "a value used in the formula is of the wrong data type". There ar etwo types of data $A$2:$A$5000 is text and $M$2:$M$5000 is a date. I want to count how many times the year 1900 shows up for a particular proposal number ($A$2:$A$5000). I use a value of 0 in the date field when I don't need to concerned with the information but I need to count it so the totals work out. 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
DATEVALUE will return an error if the cells contain true Excel dates.
DATEVALUE is for use on TEXT representations of dates. If the cells in question contain 0s just test for 0: SUMPRODUCT(--(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5000,4)=$A26),--(ISNUMBER('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000)),--('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000=0)) Biff "Joe Gieder" wrote in message ... First, sorry for the long post and thank you for helping and looking. I'm trying to use this formula: SUMPRODUCT(--(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5000,4)=$A26),(YEAR(DATEVALUE('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000)=1900))) and it returns value, the comment says "a value used in the formula is of the wrong data type". There ar etwo types of data $A$2:$A$5000 is text and $M$2:$M$5000 is a date. I want to count how many times the year 1900 shows up for a particular proposal number ($A$2:$A$5000). I use a value of 0 in the date field when I don't need to concerned with the information but I need to count it so the totals work out. 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 between dates | Excel Worksheet Functions | |||
sumproduct with dates | Excel Worksheet Functions | |||
Sumproduct using dates | Excel Worksheet Functions | |||
Receive #VALUE! when I mix text with dates using SUMPRODUCT | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions |