0

So, I've got a table with some data in a column. Let's say the column looks like this (using commas to represent the end of one cell and the start of the next):

ABC, ACD, AABBCF, EFGGHH, AAABCDE

I want to write a formula that will tell me how many times the letter A shows up in that column, across all the cells. In this case, the answer is 7 times. So, like, I want to concatenate all the cells, and then count how times A shows up in that whole long string. How would I do this?

Avi Caspe
  • 537
  • 4
  • 12
  • Does this answer your question? [Count occurrences of given character per cell](https://stackoverflow.com/questions/27154682/count-occurrences-of-given-character-per-cell) – Tedinoz Aug 15 '23 at 00:37

1 Answers1

0

Please try the following

One way:

1. Create a new Named function using this formula

=FLATTEN(SPLIT(REGEXREPLACE(CONCATENATE(range), "(.)", "$1_"), "_"))

Named Function in Google sheets

2. Use this formula in a cell

=BYROW(UNIQUE(TO_SINGLE(A92:A98),,0),LAMBDA(uni, 
          {uni,COUNTIF(TO_SINGLE(A92:A98),"="&uni)}))

This second formula will give you 2 columns. The first one with all the unique characters and the second one with their occurrence times.
(Do adjust the formulae according to your ranges and locale)

enter image description here

Another way:

Instead of using a Named function and a second formula, one can use this single formula

=LET(range, FLATTEN(SPLIT(REGEXREPLACE(CONCATENATE(A92:A98), "(.)", "$1_"), "_")),
  BYROW(SORT(UNIQUE(range,,0),1,1),LAMBDA(uni, {uni,COUNTIF(range,"="&uni)})))

enter image description here

Functions used:

marikamitsos
  • 10,264
  • 20
  • 26
  • Out of curiosity. Have you tried the given solution? Did it work for you? Was it something else you were looking for? Any kind of reaction would be highly appreciated. – marikamitsos Aug 16 '23 at 09:36