1

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

Improve this question
2
  • 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
    Commented Mar 2, 2021 at 11:41
  • No mixed characters, it will always be a single character or a repeat of a single character
    – Alfo
    Commented Mar 2, 2021 at 11:59

3 Answers 3

Reset to default
1

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:

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

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

REGEXREPLACE adding Delimiters

My addition to the Source Material:

  • "CONCATENATE($W$2:$Y$5)"
Improve this answer
1
  • @erik-tyler I suspect my solution may be more robust than yours as it doesn't require the restriction on character count or mixing of characters. I could be wrong, though. Thoughts?
    – Nicholas Mathern
    Commented Mar 2, 2021 at 12:45
0

Given your additional information that there will be "[n]o mixed characters, it will always be a single character or a repeat of a single character," this formula should produce the result you want:

=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) ''"))

IMPORTANT: In your post example, you place the results directly underneath the raw data. That is never a good idea, because you'd have to modify the location or the formula every time you add raw data. You should keep your raw-data columns allocated solely to that data and place such a formula as I've provided here to one side or the other, or in a separate sheet (understanding that the latter would require adding the raw-data sheet name to the target range in the formula).

As for how this formula works, please verify that it does, in fact, product the results you want and report back. Once I know that it's what you're after, I'll add an explanation.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.