# Sumifs

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:56 PM GMT

## Question

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