Need help with SUMIF Formula

Occasional Contributor

Hello,

 

I am trying to use a SUMIF formula to show me if an item has been completed. Please see the picture added. The highlighted are where I am trying to have the formula in.

 

Please see the logic below for Phase 1 (this is how I got 1)

If the Test Name is in "Group 1", "Group 2" or "Group 3"

AND in "Phase 1" 

AND the Completed? includes a date 

THEN it counts as 1 

 

Please see the logic below for Phase 2 (this is how I got 2)

If the Test Name is in "Group 1", "Group 2" or "Group 3"

AND in "Phase 2" 

AND the Completed? includes a date 

THEN it counts as 1 

 

Please see the logic below for Phase 3 (this is how I got 3)

If the Test Name is in "Group 1", "Group 2" or "Group 3"

AND in "Phase 3" 

AND the Completed? includes a date

THEN it counts as 1 

 

Please see the logic below for Phase 4 (this is how I got 1)

If the Test Name is in "Group 1", "Group 2" or "Group 3"

AND in "Phase 4" 

AND the Completed? includes a date

THEN it counts as 1 

 

example pic.jpg

 

2 Replies

@James_S123 

=SUMPRODUCT(($D$2:$D$15<>"")*(($B$2:$B$15="group 1")+($B$2:$B$15="group 2")+($B$2:$B$15="group 3"))*($C$2:$C$15=D18))

You can try SUMPRODUCT.

sumproduct.JPG 

Thanks for the reply! Not all non-completes are going to be blanks though which is why I had a few N/As in there