Counting Unique Values That Are Separate by Commas in a Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rothman - 25 Mar 2008 19:31 GMT

I have a column like so, with each text line equalling a cell (the names are
U.S. Counties; 11 cells are below, excerpted from my dataset of around 600
rows):

Mobile, Baldwin
Sumter, Greene, Tuscaloosa, Jefferson, St. Clair, Talladega, Calhoun, Cleburne
Sumter, Greene, Tuscaloosa, Jefferson, St. Clair, Etowah, Dekalb
Mobile, Baldwin, Escambia, Conecuh, Butler, Lowndes, Montgomery, Elmore,
Autauga, Chilton, Shelby, Jefferson, Blount, Cullman, Morgan, Limestone
Montgomery, Macon, Lee, Chambers
Mobile
Tuscaloosa
Jefferson
Limestone, Madison
Etowah

I've used this formula to count all the counties in an individual cell:

=IF(F7<>"",LEN(F7)-LEN(SUBSTITUTE(F7,",",""))+1,0)

However, now I need a state total that counts the counties in the column.
Is there any way of counting only unique values so I don't double count
counties when coming up with my state total?

Thanks again!

Reply to this Message

Rick Rothstein (MVP - VB) - 25 Mar 2008 20:14 GMT

Assuming G1 contains the name of the county you want to search for and
Column F contains your listing, try this formula...

=SUMPRODUCT(--ISNUMBER(SEARCH(G1,F1:F1000)))

Rick

>I have a column like so, with each text line equalling a cell (the names
>are
[quoted text clipped - 23 lines]
>
> Thanks again!

Reply to this Message

Rothman - 25 Mar 2008 21:06 GMT

I'm sorry, but it seems once again my powers of explanation have proven to be
inadequate.

I don't have a listing of counties that I'm comparing my column against.
All I'm looking for is a number that represents "number of counties covered"
for a particular state. Given my column, which has cells which contain more
than one county, and that the same county may appear in two or more different
cells, I just need to count the unique counties in the column (as opposed to
summing up the formula I used to count the counties per cell, which double
counts).

> Assuming G1 contains the name of the county you want to search for and
> Column F contains your listing, try this formula...
[quoted text clipped - 30 lines]
> >
> > Thanks again!

Reply to this Message