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" <(E-Mail Removed)> wrote in message
> news:18C3FB1B-EBDE-412A-9C29-(E-Mail Removed)...
> >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!
>
>
Similar Threads
