Counting occurrences in a range

  • Thread starter Thread starter nclorguy
  • Start date Start date
N

nclorguy

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?
 
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?
Click to expand...

Got the answer:
=SUM(LEN(E2:E880)-LEN(SUBSTITUTE(E2:E880,"Vermont","")))/LEN("Vermont")
 
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?
Click to expand...

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.
 
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?
Click to expand...

=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/Forums/en-US/category/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.
 
Back
Top