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

1. ## 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.

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

2. ## Re: total count of non-blank cell(s) and unique values

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

3. ## Re: total count of non-blank cell(s) and unique values

Hi there - I tried the 1st formula, it gave me a total of 19, I'm getting one more than expected. I'm attaching the spreadsheet...please let me know what I'm doing wrong. Thanks again!

4. ## Re: total count of non-blank cell(s) and unique values

LILIE on cell A17 has one space at the end of the word, that makes it different than LILIE in cell A18.

Regards

5. ## Re: total count of non-blank cell(s) and unique values

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

6. ## Re: total count of non-blank cell(s) and unique values

I've corrected the cell A17 and tried the concatenate formula, it works fine, except when I get 2 of the same users with 2 different answer on column B, it's counting both answers...I only want to count one non-blank answer for each user. Is there a way to do this? (see sample below-the total shows 19, should be 19..it's counting Lillie's answer twice). thanks!

JOSE Yes JOSEYes
MARIA No - Other Reason MARIANo - Other Reason
MARY No - Other Reason MARYNo - Other Reason
SHERYL K No - Other Reason SHERYL KNo - Other Reason
VIGGO J No - Other Reason VIGGO JNo - Other Reason
IGNACIO No - Other Reason IGNACIO No - Other Reason
ISAAC No - Other Reason ISAACNo - Other Reason
LAURA Yes LAURA Yes
MERIC No - Other Reason MERICNo - Other Reason
JUAN No - Other Reason JUAN No - Other Reason
OLGA No - Other Reason OLGA No - Other Reason
ARTHUR Yes ARTHURYes
CARLA Yes CARLAYes
JOAN No - Other Reason JOANNo - Other Reason
LILLIE Yes LILLIE Yes
LILLIE No - Other Reason LILLIENo - Other Reason
DOROTHY No - Other Reason DOROTHY No - Other Reason
JOHN C
PABLO No - Other Reason PABLONo - Other Reason
PABLO No - Other Reason PABLONo - Other Reason
PABLO No - Other Reason PABLONo - Other Reason

7. ## Re: total count of non-blank cell(s) and unique values

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

8. ## Re: total count of non-blank cell(s) and unique values

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

9. ## Re: total count of non-blank cell(s) and unique values

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

10. ## Re: total count of non-blank cell(s) and unique values

This is SOLVED, but I'm not able to get to my original post to mark it SOLVED. Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1