Google Spreadsheet, Count IF contains a string

155,255

Solution 1

It will likely have been solved by now, but I ran accross this and figured to give my input

=COUNTIF(a2:a51;"*iPad*")

The important thing is that separating parameters in google docs is using a ; and not a ,

Solution 2

In case someone is still looking for the answer, this worked for me:

=COUNTIF(A2:A51, "*" & B1 & "*")

B1 containing the iPad string.

Solution 3

You should use

=COUNTIF(A2:A51, "*iPad*")/COUNTA(A2:A51)

Additionally, if you wanted to count more than one element, like iPads OR Kindles, you would use

=SUM(COUNTIF(A2:A51, {"*iPad*", "*kindle*"}))/COUNTA(A2:A51)

in the numerator.

Solution 4

Try using wildcards directly in the COUNTIF function :

=(COUNTIF(A2:A51,"=*iPad*")/COUNTA(A2:A51))*1

Solution 5

Wildcards worked for me when the string I was searching for could be entered manually. However, I wanted to store this string in another cell and refer to it. I couldn't figure out how to do this with wildcards so I ended up doing the following:

A1 is the cell containing my search string. B and C are the columns within which I want to count the number of instances of A1, including within strings:

=COUNTIF(ARRAYFORMULA(ISNUMBER(SEARCH(A1, B:C))), TRUE)
Share:
155,255

Related videos on Youtube

Cody
Author by

Cody

Updated on February 26, 2022

Comments

  • Cody
    Cody over 2 years

    I have a column like this:

    What devices will you be using?

    iPad
    Kindle & iPad
    No Tablet
    iPad
    iPad & Windows
    

    How do I count the amount of people that said iPad?

    This formula does work for exact matches but not if it contains an additional value:

    =(COUNTIF(A2:A51,"=iPad")/COUNTA(A2:A51))*1
    

    Any Suggestions?

    • eLRuLL
      eLRuLL almost 11 years
      You can use FIND("iPad",CELL) to check if a text is inside another text.
    • Cray Kao
      Cray Kao almost 8 years
      If you have answer, please check it.
    • Wilf
      Wilf over 6 years
      @eLRuLL that appears to be for only single cells. and the below only seems to be for Google Spreadsheets only :/ EDIT: other software just handles regular expressions differently (i.e. properly)! :
  • Cody
    Cody almost 11 years
    It came up as a "Parse Error"
  • Simon
    Simon almost 11 years
    Oops.. check out my edited answer ("=*iPad*") ... the = was out of the brackets
  • Simon
    Simon almost 11 years
    @Cody did you try it out ?
  • Cody
    Cody almost 11 years
    I checked the edited answer, still coming up as a parse error.
  • alexsuslin
    alexsuslin about 9 years
    it depends on the locale
  • mike
    mike almost 9 years
    this helped me because it demonstrates how to use the wildcards combined with a cell reference, rather than a hard-coded string
  • john
    john about 8 years
    Agree with mike, usage of the wildcards instead of cell reference or hardcode make this very useful
  • Loïc Faure-Lacroix
    Loïc Faure-Lacroix over 7 years
    Nice catch for the semicolon.
  • Dan Mergens
    Dan Mergens about 6 years
    This question has already been answered. Your suggestion produces the wrong answer (only summing 'iPad' entries and excludes entries containing the 'iPad' string).