View original (French)

The COUNTIF function doesn't work.

Solved
bert-ernie Posted messages 8 Status Member -  
 Nina -
Hello,
I have a small problem with Excel. I'm working on the Excel online from Google Drive, I don't know if that makes a difference. I'm actually quite bad with computers. ;)

I'll explain from the beginning to provide all relevant (or irrelevant) elements.
I created a satisfaction survey with Google Forms. It's very simple, there are just 5 questions (welcome, cleanliness, etc.) to which people respond with "not at all satisfied," "somewhat satisfied," or "very satisfied." In reality, they respond on paper, but I transfer the answers to the computer later to make it "readable."
The answers are then stored in an Excel document ("Google Sheets" since it's online). So far, no problem.
Then I wanted to integrate a formula to count the number of "very satisfied," "somewhat satisfied," and "not at all satisfied" for each question. After some research (I'm really bad), I thought the formula to use is =COUNTIF(range, "word to count"), but it doesn't work. It shows 0 when it should be 19. I've looked on other forums, but I can't find anything that works or that really applies to my case.
Even stranger: when I enter =COUNTIF(B5:B24, "very"), it gives me 0 while when I enter =COUNTIF(B5:B24, "very satisfied"), it gives me 9. I think the 9 corresponds to the number of cells that only have "very satisfied" (with no other text).

If anyone has an idea to solve my problem, it would be very nice! ;)
Thank you so much!!

2 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    Yes, 9 is the number of texts that are exclusively "completely satisfied" found

    If you want the number of cells containing "completely" and something else afterwards, you need to indicate that with a *:
    =COUNTIF(B5:B24;"completely*")

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    5
    1. bert-ernie Posted messages 8 Status Member
       
      Thank you very much!! Super quick and efficient response! It worked for my first calculation but not for the second.
      I wanted to proceed the same way to count the "moderately satisfied" which also returned 0, but even using COUNTIF(B5:B24, "moderately satisfied*"), it shows 0 while it should return 1...
      0
    2. Nina
       

      Thank you very much!

      0
  2. jordane45 Posted messages 30426 Registration date   Status Moderator Last intervention   4 830
     
    Hello,


    Even stranger: when I use =COUNTIF(B5:B24,"totally") it gives me 0 while when I use =COUNTIF(B5:B24,"completely satisfied") it gives me 9. I think the 9 corresponds to the number of cells that contain only "completely satisfied" (without any other text).


    COUNTIF looks at the VALUE of the cell and not whether it "contains" a part ... It's an exact match search and not an approximate one.

    If you want to do a search like "the cell contains ...", you need to add a wildcard character.
    Something like 

    =COUNTIF(B5:B24,"*totally*")


    --
    Best regards,
    Jordane
    2
    1. bert-ernie Posted messages 8 Status Member
       
      Thank you very much for the response! It works! I had seen the message from via before yours but both work. :)
      Little issue: it worked for the first calculation but not for the second one with "somewhat satisfied*".
      0
      1. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > bert-ernie Posted messages 8 Status Member
         
        You need to check your file to see what isn’t working
        Post it on cjoint.com, create a link, copy it, and come back to paste it here.
        0
      2. bert-ernie Posted messages 8 Status Member > via55 Posted messages 14393 Registration date   Status Member Last intervention  
         
        I found the mistake, I had made an error in the formula and only used =COUNT instead of COUNTIF.
        So the problem is solved with your answers!
        Thank you very much
        0