How can I tally unique entries in a column?

eggplant_casserole

New Member
Joined
Jul 5, 2011
Messages
4
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:

excel.png


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.

Thanks in advance!
 

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
Joined
Dec 30, 2008
Messages
15,469
Office Version
  1. 365
Platform
  1. Windows
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,"<>")
 
Upvote 0
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top