CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Shian Han
Shian Han

Reputation: 317

How does one count specific individual text strings inside a range of cells, in Google Sheets?

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

Answers (1)

basic
basic

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)),"☑︎"))

enter image description here

Upvotes: 3

Related Questions