Selecting from multiple criterion and adding results

Question

I'm working from a spread sheet with project names, employee names, and dates (among other things). I¡¯m trying to create a formula to select from that criterion and add hours each employee worked. The formula I've got so far is:

=IF(AND(AND(AND(D2:D4="LastName, FirstName", C2:C4 > DATEVALUE("05/01/2010") ), C2:C4 <= DATEVALUE("05/15/2010")), B2:B4 <> "WrongProjectName"),F2, 0)

Unfortunately, the above formula doesn¡¯t work. I guessdoesn¡¯t like ¡°D2:D4¡±, ¡°C2:C4¡±, and ¡°B2:B4¡±. Ideas?

Answer

If you have names in D, Dates in C, project name in B and hours in F

=sumproduct(--(D2:D4="LastName, Firstname"),--(C2:C4>Datevalue("05/01/2010"),--(C2:C4<=DateValue("05/15/2010"),--(B2:B4<>"WrongProjectName"),F2:F4)

if you have2007, you can use the new sumifs function (note the "s" on the end of sumifs)

=Sumifs(F2:F4,D2:D4,"LastName, FirstName",C2:C4,">"&DateValue("05/01/2010"),C2:C4,"<="&DateValue("05/15/2010"),B2:B4,"<>WrongProjectName")

Again, the Sumifs function was added in Excel 2007, so it isn't available in earlier verisions of Excel. The sumproduct function above will work in all versions.