Sumifs

QuestionEdit

QUESTION: Good afternoon Here is my formula. =SUM(IF(($A$2:$A$1715="Able Derik W")*($H$2:$H$1715<"12/31/2007"),$I$2:$I$1715)).

What I am trying to do is create a Sumif or SumIFs formula so that when I see a particular name I will go to the check date column and look for all dates below 12/31/2007. If there are go to the hours taken column and add them together. This equation seems to be adding 2008 for some reason and I am not sure how to fix. I hope I explained myself correctly. ANSWER: Comparing it against "12/31/2007" is a string comparison, not a date comparison. Try: =SUM(IF(($A$2:$A$1715="Able Derik W")*($H$2:$H$1715<DATEVALUE("12/31/2007")),$I$2:$I$1715)) but there's a better formula and doesn't need to be array-entered: =SUMPRODUCT(N($A$2:$A$1715="Able Derik W"),N($H$2:$H$1715<DATEVALUE("12/31/2007")),$I$2:$I$1715)) or instead of DATEVALUE("12/31/2007") you can use 1*"12/31/2007": =SUMPRODUCT(N($A$2:$A$1715="Able Derik W"),N($H$2:$H$1715<1*"12/31/2007"),$I$2:$I$1715))

---------- FOLLOW-UP ----------

QUESTION: It gave me a #Value. is it possible to send you the spread sheet so maybe it will help? Thank you so much

AnswerEdit

sure - bobumlas@yahoo.com, use subject of "AllExpertsQ", make sure you direct me to the "offending" cell.