I did a survey where the data in one column looks something like this:
Texas, California, Iowa
Iowa, California, Vermont
Texas, Vermont, California

etc.

I do not want to parse the data, but rather want to count, for
example, all occurrences of Vermont. I tried count and countif but
nothing worked. Any ideas?

On Aug 29, 9:43*am, nclorguy <thermomete...@gmail.com> wrote:
> I did a survey where the data in one column looks something like this:
> Texas, California, Iowa
> Iowa, California, Vermont
> Texas, Vermont, California
>
> etc.
>
> I do not want to parse the data, but rather want to count, for
> example, all occurrences of Vermont. *I tried count and countif but
> nothing worked. *Any ideas?

Got the answer:
=SUM(LEN(E2:E880)-LEN(SUBSTITUTE(E2:E880,"Vermont","")))/LEN("Vermont")

On Sun, 29 Aug 2010 06:43:17 -0700 (PDT), nclorguy <(E-Mail Removed)> wrote:
> I did a survey where the data in one column looks something like this:
> Texas, California, Iowa
> Iowa, California, Vermont
> Texas, Vermont, California
>
> etc.
>
> I do not want to parse the data, but rather want to count, for
> example, all occurrences of Vermont. I tried count and countif but
> nothing worked. Any ideas?

Try countifs...I put up a 3x3 grid with those states, and used
"countifs($a$1:$c$3,"=Vermont")", which produced 2. Replace that with
"=California" and you'll get 3.

--
Patrick L. "The Chief Instigator" Humphrey ((E-Mail Removed)) Houston, TX
www.io.com/~patrick/aeros.php (TCI's 2009-10 Houston Aeros) AA#2273
LAST GAME: San Antonio 3, Houston 2 (April 11)
NEXT GAME: Saturday, October 9 at Oklahoma City, 7:05

On Aug 29, 6:43*am, nclorguy <thermomete...@gmail.com> wrote:
> I did a survey where the data in one column looks something like this:
> Texas, California, Iowa
> Iowa, California, Vermont
> Texas, Vermont, California
> etc.
>
> I do not want to parse the data, but rather want to count, for
> example, all occurrences of Vermont. *I tried count and countif but
> nothing worked. *Any ideas?

=COUNTIF(A1:A100,"*vermont*")

should do the trick. If that does not work for you, provide example
data, the formula you tried (copy-and-pasted from the Formula Bar),
the result you got, and the result you want.

Some caveats:

1. It is important that "vermont" is not a substring of a longer word
that you do not want to count. Is that an issue for you?

2. The COUNTIF solution will count multiple occurrences in the same
cell as only one. Is that an issue for you?

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/...ry/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.