Counting Unique Values That Are Separate by Commas in a Column
 

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!

Counting Unique Values That Are Separate by Commas in a Column
 

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


"Rothman" wrote in message
...
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!

Counting Unique Values That Are Separate by Commas in a Column
 

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).



"Rick Rothstein (MVP - VB)" wrote:

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


"Rothman" wrote in message
...
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!