total count of non-blank cell(s) and unique values

Good afternoon - I have the spreadsheet bleow (it's also attached for better layout), and what I want it to get is the total count of non-blank cell from the answer(column B) and only count the answer once for those users that are listed multiple times (columnA).So my total count fro ColumnB should be 18. Please let me know how can I accomplish this.
Thank you in advance!

User Answer
JOSE Yes
MARIA No - Other Reason
MARY No - Other Reason
SHERYL K No - Other Reason
VIGGO J No - Other Reason
IGNACIO No - Other Reason
ISAAC No - Other Reason
LAURA Yes
GUADA No - Other Reason
MERIC No - Other Reason
JUAN No - Other Reason
OLGA No - Other Reason
ARTHUR Yes
CARLA Yes
JOAN No - Other Reason
LILLIE Yes
LILLIE Yes
DOROTHY No - Other Reason
JOHN C
PABLO No - Other Reason
PABLO No - Other Reason
PABLO No - Other Reason

Perhaps something like:

=COUNT(1/FREQUENCY(IF(A2:A23<>"",IF(B2:B23<>"",MATCH(A2:A23&B2:B23,A2:A23&B2:B23,0))),ROW(A2:A23)-ROW(A2)+1))

adjust ranges to suit and confirm with CTRL+SHIFT+ENTER not just ENTER.

another less complicated way may be to concatenate the column A and B items into Column C with fomrula like: =A2&B2 copied down

Then apply formula: =SUMPRODUCT((C2:C23<>"")/COUNTIF(C2:C23,C2:C23&"")) with just ENTER

FWIW, SUMPRODUCT wise you could also use the below without need for concatenation:

=SUMPRODUCT(--($A$2:$A$23<>""),--($B$2:$B$23<>""),--(MATCH($A$2:$A$23&"@"&$B$2:$B$23,$A$2:$A$23&"@"&$B$2:$B$23,0)=(ROW($A$2:$A$23)-ROW($A$2)+1)))

Are you looking to calculate the number of unique names that have offered at least one answer ?

FWIW - when concatenating strings it's generally a good idea to concatenate with a delimiter (that does not otherwise appear in the strings), eg:

C2: =A2&"@"&B2
copied down

This reduces risk of reporting false positive matches in the COUNT.


Better to post sample files by the way...

Originally Posted by esanchezz

I only want to count one non-blank answer for each user

In which case, adopting the concatenation approach - I would suggest you modify your concatenation to:

C2: =$A2&"@"&($B2<>"")
copied down to C23

At which point your formula becomes:

=SUMPRODUCT(($B$2:$B$23<>"")/COUNTIF($C$2:$C$23,$C$2:$C$23&""))

And based on your data I would assume the correct result to be 17 - ie their are 17 unique names in the list that have offered at least one answer
(Dorothy, John C though unique have not offered an answer and are thus discounted).

And again make sure there are no lingering spaces at the end of any of the entries in Columns A and B