Reputation: 317
Let's say I have the following three rows under one column (column A):
☑︎ ☑︎ ☑︎ ☑︎ ☑︎ ☑︎ ☐ ☐
☑︎ ☑︎ ☑︎ ☐ ☐ ☐ ☐ ☐
☑︎ ☑︎ ☐ ☐ ☐ ☐ ☐ ☐
I'm looking to count the number of ☑︎ in the range A1 to A3, in this case it would be "11".
I tried the formula =COUNTA(A1:A3,"☑︎") but strangely, it returns "4".
Why is that, and more importantly, how does one get the correct answer of "11"?
Upvotes: 0
Views: 63
Reputation: 11968
You should split text and then use COUNTIF
:
=ArrayFormula(COUNTIF(SPLIT(A1:A3," "),"☑︎"))
If there is no space between the checkboxes, the formula will become more complicated:
=ArrayFormula(COUNTIF(SPLIT(REGEXREPLACE(REGEXREPLACE(A1:A3&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127)),"☑︎"))
Upvotes: 3