Google Spreadsheet, Count IF contains a string

Question

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?

You can use `FIND("iPad",CELL)` to check if a text is inside another text. — eLRuLL, Jun 17 '13 at 16:52
@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)! : — Wilf, Dec 19 '17 at 20:51

score 127 · Answer 1 · answered Nov 12 '13 at 08:25

127

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 ,

answered Nov 12 '13 at 08:25

Perry

2,250
2
19
24

9

it depends on the locale – alexsuslin Apr 23 '15 at 12:01
2

Nice catch for the semicolon. – Loïc Faure-Lacroix Feb 23 '17 at 17:46

score 86 · Answer 2 · edited Jan 02 '21 at 22:52

86

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

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

B1 containing the iPad string.

edited Jan 02 '21 at 22:52

David

139
1
1
12

answered Dec 08 '14 at 14:40

user2983604

861
6
2

9

this helped me because it demonstrates how to use the wildcards combined with a cell reference, rather than a hard-coded string – mike Aug 26 '15 at 00:18
5

Agree with mike, usage of the wildcards instead of cell reference or hardcode make this very useful – john Mar 21 '16 at 04:22
Note that this will ONLY count the number of cells that contain "iPad" and something else, so e.g. "iPad, iPhone, Samsung A32" but NOT the ones which contain "iPad" exactly. So you might need =COUNTIF(A2:A51, "*" & B1 & "*") + COUNTIF(A2:A51, B1) in the end. – ezmegy Nov 30 '22 at 13:12

YPCrumble · Answer 3 · 2013-12-31T17:24:29.617

13

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.

edited Dec 31 '13 at 17:24

answered Dec 06 '13 at 17:09

YPCrumble

26,610
23
107
172

Simon · Answer 4 · 2013-06-19T12:59:11.867

5

Try using wildcards directly in the COUNTIF function :

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

edited Jun 19 '13 at 12:59

answered Jun 17 '13 at 16:50

Simon

1,605
13
22

It came up as a "Parse Error" – Cody Jun 18 '13 at 18:32
Oops.. check out my edited answer ("=*iPad*") ... the = was out of the brackets – Simon Jun 19 '13 at 12:59
@Cody did you try it out ? – Simon Jun 19 '13 at 17:07
I checked the edited answer, still coming up as a parse error. – Cody Jun 20 '13 at 16:13

score 4 · Answer 5 · answered Feb 23 '14 at 15:45

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)

score 0 · Answer 6 · edited Feb 25 '22 at 18:55

I had similar problem however the various count solutions still wouldn't work even with wildcards of "*"& etc..... My problem was caused by &nbsb (hidden spaces) which is hidden in the background when copying eg data from a webpage. This prevents the find from working properly. Use:

 =TRIM(SUBSTITUTE(A2, CHAR(160), " "))

or

 =TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))

or similar to get rid of them.

score -7 · Answer 7 · edited Feb 15 '16 at 16:39

-7

Try just =COUNTIF(A2:A51,"iPad")

edited Feb 15 '16 at 16:39

jherax

5,238
5
38
50

answered Jul 15 '13 at 15:46

Kevin

173
1
5

1

This question has already been answered. Your suggestion produces the wrong answer (only summing 'iPad' entries and excludes entries containing the 'iPad' string). – Dan Mergens Mar 22 '18 at 06:06

Google Spreadsheet, Count IF contains a string

7 Answers7

Linked

Related