Google Spreadsheet, Count IF contains a string
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)
Related videos on Youtube
Cody
Updated on February 26, 2022Comments
-
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 almost 11 yearsYou can use
FIND("iPad",CELL)
to check if a text is inside another text. -
Cray Kao almost 8 yearsIf you have answer, please check it.
-
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 almost 11 yearsIt came up as a "Parse Error"
-
Simon almost 11 yearsOops.. check out my edited answer ("=*iPad*") ... the = was out of the brackets
-
Simon almost 11 years@Cody did you try it out ?
-
Cody almost 11 yearsI checked the edited answer, still coming up as a parse error.
-
alexsuslin about 9 yearsit depends on the locale
-
mike almost 9 yearsthis helped me because it demonstrates how to use the wildcards combined with a cell reference, rather than a hard-coded string
-
john about 8 yearsAgree with mike, usage of the wildcards instead of cell reference or hardcode make this very useful
-
Loïc Faure-Lacroix over 7 yearsNice catch for the semicolon.
-
Dan Mergens about 6 yearsThis question has already been answered. Your suggestion produces the wrong answer (only summing 'iPad' entries and excludes entries containing the 'iPad' string).