Google Sheets – Count Instances of Character in a Column

formulasgoogle sheetsgoogle-sheets-arraysgoogle-sheets-queryregex

Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character?

Below is an outline of what I'm trying to do.

Note that no character appears in more than one column, so I don't have to search all columns for all characters, I can just search the Z column for x, search the W column for a and b, etc.

Thanks

enter image description here

Best Answer

EDIT (based on your comment)

No mixed characters, it will always be a single character or a repeat of a single character

For a draggable formula use

=SUM(INDEX(IFERROR(SEARCH(B10,$B$2:$E$7)*LEN($B$2:$E$7))))

Original answer

For a single auto-expanding formula please use

=INDEX(IFERROR(VLOOKUP(B10:B13, 
          QUERY(FLATTEN(SPLIT(REGEXREPLACE(TEXTJOIN("@",1,B2:E7),"()","$1@"),"@")),
             "select Col1, count(Col1) 
              group by Col1 Label count(Col1) '' "),2,0)))

enter image description here

(do adjust ranges and locale syntax to meet your needs)

What the formula does:
The formula counts the number of instances of every single character in a range and returns them in a specified order.

Functions used:

Related Topic