Count instances of character in a column

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

• Are results in any one cell always the same character (e.g., bb, xxx) or do you have mixed characters in some cells (e.g., xax, yb)? And in your real-world application, is it really just one to three characters? Or is this going to be whole words, phrases, sentences, etc.? – Erik Tyler Mar 2 at 11:41
• No mixed characters, it will always be a single character or a repeat of a single character – Alfo Mar 2 at 11:59

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))))
``````

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)))
``````

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:

• Your draggable formula is exactly what I was looking for, thank you! – Alfo Mar 2 at 17:28
• Glad I could help and thank you for taking the time to accept. – marikamitsos Mar 2 at 23:30

Try this formula out?

``````=ArrayFormula(SUM(IF(ISNUMBER(FIND(\$W7, SPLIT(REGEXREPLACE(CONCATENATE(\$W\$2:\$Y\$5),".{1}", "\$0,"), ","))),1,0)))
``````

Sources:

Finding occurrences of a String in a Cell

`=ArrayFormula(QUERY(FLATTEN(SPLIT(REGEXREPLACE(QUERY(FLATTEN(W2:Y),"SELECT * WHERE Col1 Is Not Null"),"(.)","\$1~"),"~",0,1)),"SELECT Col1, COUNT(Col1) WHERE Col1 Is Not Null GROUP BY Col1 Label COUNT(Col1) ''"))`