# Counting Unique Values by Person

#### mathewrtaylor

##### New Member
Hey Team, I've been racking my brain on this, and either I'm over complicating or overlooking it.

Here's the scenario:

Bart 1
Bart 1

I'd like to count the number of unique values (regardless of what the numbers are). The desired output is: Addy 4, Bart 1. I've been trying to see how to do this, but other than adding an identifier along the original data (which I don't particularly want to do) and adding that number, I can't see an alternative. Any suggestions or recommendations?

This site has been a primary source of knowledge for me over the years and the first time I couldn't find an answer, so finally registered!

Mathew

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the Board!

One way would be to use the Remove Duplicates functionality to remove all the duplicates, and then count the remaining names.
You might be able to use Pivot Tables or Subtotals to get those counts for each person without having to create a formula for each one or use VBA.

maybe something like...

E2 =SUM(IF(FREQUENCY(IF((\$A\$2:\$A\$9=D2)*(\$B\$2:\$B\$9<>""),\$B\$2:\$B\$9),\$B\$2:\$B\$9),1)) Control shift enter

<TBODY>
</TBODY>

Welcome to the Board!

One way would be to use the Remove Duplicates functionality to remove all the duplicates, and then count the remaining names.
You might be able to use Pivot Tables or Subtotals to get those counts for each person without having to create a formula for each one or use VBA.

I'm really trying not to do any destructive editing, so trying to avoid VBE though certainly a last ditch effort. Thanks for the suggestion!

Mathew

maybe something like...

E2 =SUM(IF(FREQUENCY(IF((\$A\$2:\$A\$9=D2)*(\$B\$2:\$B\$9<>""),\$B\$2:\$B\$9),\$B\$2:\$B\$9),1)) Control shift enter

<tbody>
</tbody>

This works like a charm! This is an area where I don't have a great breadth of work; array formulas. Thanks greatly for the suggestion, solved my issue!

Mathew

you're welcome, glad it worked for you

Weazel's suggestion does not involve any VBA or deleting/removing any data.
What might be a pain is coming up with the list of unique names in column D (if your data is quite long).
However, there is a quick formulaic way you can do that too. See: How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

So if you combine that with Weazel's suggestion, you should be able to get what you want without any VBA or without having to delete out any data rows.

Replies
5
Views
747
Replies
1
Views
218
Replies
7
Views
992
Replies
5
Views
228
Replies
2
Views
235

1,196,053
Messages
6,013,119
Members
441,749
Latest member
batel19

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back