# Need unique count of text from multiple columns by category

1. ## Need unique count of text from multiple columns by category

Hi,

My data looks something like (assume begins in A1):
Category,Label1,Label2
Dogs,blank,doe
Dogs,zoo,blank
Cats,zoo,yup
Cats,doe,hgg
Cats,blank,doe

What the result should look like:
Dogs, 2
Cats, 4

My actual data has 100s of rows and 5 label columns.

I'm looking for a solution that either uses sumproduct or an array formula and can handle blanks. I don't want to create a UDF in VBA.

The closest I got is with this formula which got me .83: SUM(((\$A\$2:\$A\$6="Dogs")*(\$B\$2:\$C\$6<>""))/(COUNTIF(\$B\$2:\$C\$6,\$B\$2:\$C\$6&"")))

I've spent the better part of the day researching this and trying to solve this. I've found solutions that were close to what I was looking for but was not able to successfully modify them.

Thanks
Dan

2. ## Re: Need unique count of text from multiple columns by category

Why should Cats be 4?? Doesn't this give you the result that you need?

=COUNTIF(A1:C5,"Dogs")

3. ## Re: Need unique count of text from multiple columns by category

Because I am looking for the unique count of labels by category.

In this case, between both "label" columns, Cats has a total of 4 unique labels: zoe, yup, hgg, doe

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