#Value or #DIV/0! Error

[NewUser1070]

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))

 

[xld]

Try this array formula

=SUM(--(FREQUENCY(IF(C2:C10="Yes",MATCH(B2:B10,B2:B10,0)),ROW(INDIRECT("1:"&ROWS(B2:B10))))>0))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B2:B10<>"",IF(C2:C10="Yes",
   MATCH("~"&B2:B10,B2:B10&"",0))),ROW(B2:B10)-ROW(B2)+1),1))

which won't be affected by empty or blank cells.

If you have the relevant ranges defined as STUDENTS and TAGS...

=SUM(IF(FREQUENCY(IF(STUDENTS<>"",IF(TAGS="Yes",
   MATCH("~"&STUDENTS,STUDENTS&"",0))),
    ROW(STUDENTS)-MIN(ROW(STUDENTS))+1),1))

Note. Remove the "~"& and &"" bits from the formula for the student names presumably will not contain chars like < or *, etc.

 

[NewUser1070]

Both of these solutions still cause a #Value error

 

[Aladin Akyurek]

Both of these solutions still cause a #Value error

You need to confirm the formula with control+shift+enter (the combination of three keys mentioned), not just with enter.

 

[NewUser1070]

GREAT that worked!! What does the ctl+shift+enter do that enter doesn't do?

 

[Aladin Akyurek]

GREAT that worked!! What does the ctl+shift+enter do that enter doesn't do?

The combination is a signal for Excel that the formula is of a particular type. See "array formula" in Excel's Help.