I’m working with a column of text data in Google Sheets and need help finding which text appears most often. My data is in cells C3:C8 and contains various text entries like colors or categories.
For instance, if my range has these values: {“apple”, “banana”, “apple”, “orange”, “apple”, “banana”}, I want a formula that will show “apple” since it appears 3 times (more than any other value).
What function or formula can I use to identify and display the most frequently occurring text value from this range? I’ve tried a few approaches but can’t seem to get it working correctly.
Both formulas work, but I hit problems with ties when multiple values showed up the same number of times. Here’s what I use now: =QUERY(C3:C8,“select C, count(C) group by C order by count(C) desc limit 1”,0). It gives you the most common value AND its count, plus handles ties by picking the first one alphabetically. I switched to this after array formulas kept breaking on datasets with duplicate max frequencies. QUERY’s also way easier to tweak - want the second most common? Just change limit 1 to limit 2. Works reliably across different Sheets versions without the array formula headaches.
MODE won’t work here - it’s for numbers, not text. You need COUNTIF with INDEX/MATCH instead. Try this: =INDEX(C3:C8,MODE(MATCH(C3:C8,C3:C8,0))). It creates position matches for each text value, then finds the mode of those positions. I’ve used this tons of times for survey responses and categorical data. Basically finds which text appears most by looking at match positions. If you’re on an older Sheets version, enter it as an array formula. Way better than trying to force number functions on text.
there’s actually an easier way - just use =MODE(COUNTIF(C3:C8,C3:C8)) as an array formula. works great with text and you don’t need all that INDEX/MATCH stuff. i’ve been using this on my inventory sheets for years.