R
Rothman
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!
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!