Counting occurences of text
 

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

If your range of answers is in A1 to A1000 then use 4 COUNTIF formulae in 4
other cells

=COUNTIF(A1:A1000,"very good")
=COUNTIF(A1:A1000,"good")
=COUNTIF(A1:A1000,"not good")
=COUNTIF(A1:A1000,"not good at all")

"DJ" wrote:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

Without seeing the data it would be difficult to suggest but will the below
work?

=COUNTIF(A:A,"*good*")-COUNTIF(A:A,"*not*")

OR
'count all responses minus count entries having not, bad etc;
=COUNTA(A:A)-COUNTIF(A:A,"*not*")

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

Try this€¦

=COUNTIF(A1:A50,"GOOD")
=COUNTIF(A1:A50,"VERY GOOD")

Here I have mentioned the range as A1:A50 change the cell range to your
desired Range and in Criteria I have mentioned it as "GOOD". Paste the same
formula in the next cell and change the word GOOD to "VERY GOOD". Like that
mention the keyword in the Criteria.

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------



"DJ" wrote:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

What is your data layout?
If very good, good, not good, not good at all are in, say column A in
separate cells like below

minÅ‘sÃ*tés
very good
good
not good
not good at all
good
good
good
not good
not good
not good
not good
not good at all
not good at all
not good at all
very good
very good
very good
very good
very good

Then
count them in this way:

Place different values in, say column B, and enter formula in C1:

=COUNTIF(A:A,B1)

Result shall look like this:

B C
very good 6
good 4
not good 5
not good at all 4

Regards,
Stefi


€žDJ€� ezt Ã*rta:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

Thanks but didn't work - here is formula I have -
=SUMPRODUCT(--(ISNUMBER(SEARCH("good",raw_data!$E$2:$E$8002))*(--(ISNUMBER(SEARCH(B4,LEFT(raw_data!$W$2:$W$8002,2)) ))))).

What I am trying to do is match how many people said GOOD (for example) from
a particular division column W. If I put in countif it messes it up...

--
DJ


"Jacob Skaria" wrote:

Without seeing the data it would be difficult to suggest but will the below
work?

=COUNTIF(A:A,"*good*")-COUNTIF(A:A,"*not*")

OR
'count all responses minus count entries having not, bad etc;
=COUNTA(A:A)-COUNTIF(A:A,"*not*")

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

--Without seeing the data you have in ColW; it is difficult to analyze why
the formula is not working. With cell B4 = 'NY' and with the below data in
ColE and ColW the formula returns the count of entries having a "good" and
which start with 'NY' in ColW. Is that what you meant?

=SUMPRODUCT(--(ISNUMBER(SEARCH("good",raw_data!$E$2:$E$8002))*(--(ISNUMBER(SEARCH(B4,LEFT(raw_data!$G$2:$G$8002,2)) )))))

Col E Col W
good NY_001
very good NY_002
not good NY_003
very very good NY_004
notbad NY_004

If so you can use try the below version
=SUMPRODUCT((ISNUMBER(SEARCH("good",raw_data!$E$2: $E$8002))*(LEFT(raw_data!$W$2:$W$8002,2)=B4)))

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

Thanks but didn't work - here is formula I have -
=SUMPRODUCT(--(ISNUMBER(SEARCH("good",raw_data!$E$2:$E$8002))*(--(ISNUMBER(SEARCH(B4,LEFT(raw_data!$W$2:$W$8002,2)) ))))).

What I am trying to do is match how many people said GOOD (for example) from
a particular division column W. If I put in countif it messes it up...

--
DJ


"Jacob Skaria" wrote:

Without seeing the data it would be difficult to suggest but will the below
work?

=COUNTIF(A:A,"*good*")-COUNTIF(A:A,"*not*")

OR
'count all responses minus count entries having not, bad etc;
=COUNTA(A:A)-COUNTIF(A:A,"*not*")

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ

Counting occurences of text
 

If column W contains department names and B4 contains dept name you are
querying then

=SUMPRODUCT(--(raw_data!$E$2:$E$8002="good"),--(raw_data!$W$2:$W$8002=B4))

=SUMPRODUCT(--(raw_data!$E$2:$E$8002="very
good"),--(raw_data!$W$2:$W$8002=B4))

etc.

Regards,
Stefi

€žDJ€� ezt Ã*rta:

Thanks but didn't work - here is formula I have -
=SUMPRODUCT(--(ISNUMBER(SEARCH("good",raw_data!$E$2:$E$8002))*(--(ISNUMBER(SEARCH(B4,LEFT(raw_data!$W$2:$W$8002,2)) ))))).

What I am trying to do is match how many people said GOOD (for example) from
a particular division column W. If I put in countif it messes it up...

--
DJ


"Jacob Skaria" wrote:

Without seeing the data it would be difficult to suggest but will the below
work?

=COUNTIF(A:A,"*good*")-COUNTIF(A:A,"*not*")

OR
'count all responses minus count entries having not, bad etc;
=COUNTA(A:A)-COUNTIF(A:A,"*not*")

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

Got huge list of responses to survey - they a very good, good, not good,
not good at all. Problem I have is that when I try to count how many instance
of say good there are sheet counts all that have good i.e. very good, not
very good etc - can anyone help me?
--
DJ