# Count the number of Unique Items in a Column for each Unique Item in another Column

1. ## Count the number of Unique Items in a Column for each Unique Item in another Column

Is there any way to do a count of the number of unique items in Column B for each unique item in column A without using a helper column and a Pivot Table.

I'm using this formula in a helper column follwed by a pivot table to give me the results. I'm trying to achieve the same result without either the helper column or the pivot table in the attached sample.

``Please Login or Register  to view this content.``
Many thanks

2. ## Re: Count the number of Unique Items in a Column for each Unique Item in another Column

Enter formula in F12 and copy down

=SUMPRODUCT((\$A\$2:\$A\$27=E12)/COUNTIFS(\$A\$2:\$A\$27,\$A\$2:\$A\$27,\$B\$2:\$B\$27,\$B\$2:\$B\$27&""))

3. ## Re: Count the number of Unique Items in a Column for each Unique Item in another Column

That was quick... works perfectly many thanks.

Is the &"" to allow for any blanks and prevent a #DIV/0! error?

4. ## Re: Count the number of Unique Items in a Column for each Unique Item in another Column

Originally Posted by HangMan
That was quick... works perfectly many thanks.

Is the &"" to allow for any blanks and prevent a #DIV/0! error?
Yes, it is.

5. ## Re: Count the number of Unique Items in a Column for each Unique Item in another Column

Whilst it does the job perfectly, it is incredibly slow to calculate when dealing with around 5,000 rows, is that what you would expect?

6. ## Re: Count the number of Unique Items in a Column for each Unique Item in another Column

Originally Posted by HangMan
Whilst it does the job perfectly, it is incredibly slow to calculate when dealing with around 5,000 rows, is that what you would expect?
Try this array formula. It should be better speed-vise than SUMPRODUCT

=SUM(IF(FREQUENCY(IF(A\$2:A\$27=E12,MATCH(B\$2:B\$27,B\$2:B\$27,0)),ROW(B\$2:B\$27)-MIN(ROW(B\$2:B\$27))+1),1))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

7. ## Re: Count the number of Unique Items in a Column for each Unique Item in another Column

Ah, I didn't even have time to make a cup of coffee running this one

Much quicker, many thanks for the quick reply as well...

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