Counting occurences of text

  • Thread starter Thread starter DJ
  • Start date Start date
D

DJ

Got huge list of responses to survey - they are: 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?
 
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")
 
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
 
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!
 
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:
 
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...
 
--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 versio
=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
 
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:
 
Back
Top