Thread: SUMPRODUCT not working when adding 3rd criteria

First, you don't need to look twice for (--($F4:$F$2685="Complete")

Also, it counts how many times is in E column at the same time: Document-NTP Checklist, Form-Entitlement Verification Form AND Approval-ApprovalSDM... Which is 0.

You need to separate those criterias.

But, since you using XL2007 you shhould rather use COUNTIFS formula:

=COUNTIFS($E$4:$E$2685, "Document-NTP Checklist", $F4:$F$2685, "Complete", G4:G2685, "<>"&"")+COUNTIFS($E$4:$E$2685, "Form-Entitlement Verification Form", $F4:$F$2685, "Complete", G4:G2685, "<>"&"")+COUNTIFS($E$4:$E$2685, "Approval-ApprovalSDM", $F4:$F$2685, "Complete", G4:G2685, "<>"&"")

If I made some mistake in the formula hope you'll figure it out...

The way I interpret this you are trying to count the number of different projects where task A is complete and task B is complete but task C is not.

I think you need some helper cells

lets assume you put the task names A, B and C in Y4, Y5 and Y6 respectively.

Now put this formula in Z4 copied down to Z2685

=IF(AND(COUNTIFS(A$4:A4,A4,E$4:E4,Y$4,F$4:F4, "Complete")*COUNTIFS(A$4:A4,A4,E$4:E4,Y$5,F$4:F4, "Complete")=1,F4="Complete",OR(E4=Y$4,E4=Y$5), COUNTIFS(A$4:A$2685,A4,E$4:E$2685,Y$6,F$4:F$2685, "Complete")=0),"x","")

Now you just count the "x"s in column Z for the final result, i.e. in Y7

=COUNTIF(Z4:Z2685,"x")