3

I've tried different solutions from Google but none worked.

I want to extract the most frequently occurring text from a column.

I was trying the following formula but it only returns a blank cell which of course would the most frequently occurring value:

=let(range,IR8:IR,unique(filter(range,countif(range,range)=max(countif(range,range)))))
IR
AA
BA
AB
AA
BB

Based on the data above, the answer should be AA because it occurs twice whereas the others occur only once.

All the data comes from a separate formula.

How can I fix the formula?

1 Answer 1

4

Your formula appears correct however perhaps your data includes hidden empty text strings, "", or worse, spaces " ".

Users sometimes introduce them unnecessarily in Sheets formulas instead of using empty values:

Truly Empty?
------------------
✅  IFERROR(1/0)

✅  IFERROR(1/0,)

❌  IFERROR(1/0,"")

✅  IF(TRUE, x, )

❌  IF(TRUE, x, "") 

To remove both empty strings as well as values that contain only spaces use the following FILTER that includes the TRIM function which removes leading and trailing spaces. I have also incorporated it into the formulas that follow.

=FILTER(IR8:IR, TRIM(IR8:IR)<>"")

UNIQUE w/ SORTN

=LET(arr, FILTER(IR8:IR, TRIM(IR8:IR)<>""),
   UNIQUE(SORTN(arr,1,1,COUNTIF(arr ,arr), 0 )))

UNIQUE w/ FILTER

=LET(arr, FILTER(IR8:IR, TRIM(IR8:IR)<>""),
   UNIQUE(
     FILTER(arr, COUNTIF(arr, arr)=MAX(COUNTIF(arr, arr)))))

Functions

COUNTIF  FILTER  LET  MAX  SORTN  TRIM  UNIQUE

6
  • I've tried this 2 formula, but it turns out it's the same, it's showing an empty answer Commented May 7 at 14:45
  • As all the cell in IR below will have formulas to return "" in it without data from the same row, maybe there is another way? Commented May 7 at 14:46
  • even if I change my formula in IR from returning iferror(X,"") to iferror(X,), the results were the same, and if I change the formula to iferror(X), it will return FALSE which is also not I want Commented May 7 at 14:50
  • I think some of your formulas may have values in them. If all your formulas have incorporated "" it is likely that at least some of them may also have a space introduced " " instead of "" If that's the case you are providing another reason why you should not be returning empty strings unnecessarily when empty values are what you want. This whole problem is likely related to that practice. I have updated the formulas to remove empty strings as well as values that contain only spaces. I suspect this will address your issues. Please confirm.
    – Blindspots
    Commented May 7 at 18:11
  • 1
    I actually found out that after I use ,) to end instead of ,"") to end the answers, the filter answer appear! Thx a lot! Always adding "" at the end of a formula seems like a newbie mistake that I should never do it again. After all as u said, I want the cell to be TRUELY empty if there is no result. Commented May 13 at 5:05

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.