谷歌电子表格，计数 IF 包含一个字符串

我有一个这样的专栏:

你将使用什么设备？

iPad
Kindle & iPad
No Tablet
iPad
iPad & Windows

我该如何计算说 iPad 的人数？

这个公式确实适用于精确匹配，但是如果它包含一个附加值就不适用:

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

有什么建议吗？

162393 次浏览
小开

Try using wildcards directly in the COUNTIF function : 

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

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

小开

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 ,

小开

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.

小开

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)
小开

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

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

B1 containing the iPad string.

小开

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.


提交答案