# How can I tally unique entries in a column?

#### eggplant_casserole

##### New Member
I have a worksheet (Microsoft 365 Family) with a long list of items (over 2,000). It is an inventory. The items are stored in various locations, with a column for each location. Some items will be duplicated in multiple locations.

The entry in the column differs depending on condition. It may simply be a YES but it could say INCOMPLETE or something like that. Consequently, I used COUNTA to do a count of how many items are in a location.

What I want to do is have a record of how many unique items there are in a location (that is, a cell in the column is counted if it isn't blank and if its neighbouring cell in the other qualifying columns is blank). How do I go about this? As an example:

Here, the relevant columns are D to H, and rows 2 to 11. The last row should read 1, 1, 2, 2, 1 as there are duplicates in rows 2, 5 and 10.

It might also be useful to have a tally of how many items are duplicated in more than one location, which would obviously be one result rather than a result per column.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jasonb75

##### Well-known Member
Something like this maybe?
Book1
CDEFGH
23YESYESYES
31YES
41YES
52YESYES
61YES
71YES
81YES
91YES
102YESYES
111YES
1211221
Sheet1
Cell Formulas
RangeFormula
C2:C11C2=COUNTA(D2:H2)
D12:H12D12=COUNTIFS(\$C\$2:\$C\$11,1,D\$2:D\$11,"<>")

#### eggplant_casserole

##### New Member
Something like this maybe?
Brilliantly simple, thanks! I was trying to over-complicate things and confusing myself.

The COUNTA result of each column is then easy to tally where it is above 1, too, creating a total of non-unique items. That's perfect!

Replies
0
Views
100
Replies
5
Views
253
Replies
11
Views
356
Replies
3
Views
152
Replies
3
Views
155

1,186,440
Messages
5,957,843
Members
438,325
Latest member
fanofstuff

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