NewUser1070
New Member
- Joined
- Nov 8, 2010
- Messages
- 3
I want to count the number students (Column B) that completed a task (Column C). But I only want to count the student one time. I also need to be able to account for blank/null cells because I am not sure how many entries will be in my sheet.
Here is a data:
Student 1 Yes
Student 2 Yes
Student 3 Yes
Student 4 No
Student 1 No
Student 2 Yes
Student 3 Yes
Student 4 No
So my result I am looking for is 3.
Here is the formula I tried and received the #DIV/0! error:
=SUMPRODUCT(--(C2:C10="Yes"),1/COUNT(IF(B2:B10="",0,B2:B10),IF(B2:B10="",0,B2:B10),IF(C2:C10="",0,C2:C10),IF(C2:C10="",0,C2:C10)))
Here is the formula I tried and recieved a #VALUE error:
=SUMPRODUCT(--(C2:C10="Yes"),1/COUNTIFS(IF(B2:B10="",0,B2:B10),IF(B2:B10="",0,B2:B10),IF(C2:C10="",0,C2:C10),IF(C2:C10="",0,C2:C10)))
This works correctly, but only includes cells with data:
=SUMPRODUCT(--(C2:C9="Yes"),1/COUNTIFS(B2:B9,B2:B9,C2:C9,C2:C9))
Here is a data:
Student 1 Yes
Student 2 Yes
Student 3 Yes
Student 4 No
Student 1 No
Student 2 Yes
Student 3 Yes
Student 4 No
So my result I am looking for is 3.
Here is the formula I tried and received the #DIV/0! error:
=SUMPRODUCT(--(C2:C10="Yes"),1/COUNT(IF(B2:B10="",0,B2:B10),IF(B2:B10="",0,B2:B10),IF(C2:C10="",0,C2:C10),IF(C2:C10="",0,C2:C10)))
Here is the formula I tried and recieved a #VALUE error:
=SUMPRODUCT(--(C2:C10="Yes"),1/COUNTIFS(IF(B2:B10="",0,B2:B10),IF(B2:B10="",0,B2:B10),IF(C2:C10="",0,C2:C10),IF(C2:C10="",0,C2:C10)))
This works correctly, but only includes cells with data:
=SUMPRODUCT(--(C2:C9="Yes"),1/COUNTIFS(B2:B9,B2:B9,C2:C9,C2:C9))