Excel - How to Count Letter Occurrences in Excel using Formulas | Excel Tutorial - Episode 1148

1,456 views · Published 19 November 2009 · 4:29 · Indexed 14 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Count Letter Occurrences in Excel using Formulas | Excel Tutorial.

Welcome back to the MrExcel netcast, where we tackle all your Excel problems and provide you with the best solutions. In today's episode, we have a question from Ram, who needs to break down a word into individual letters and count how many times each letter occurs. Sounds simple, right? Well, let's see how we can solve this problem using some cool formulas.

First, we use the MID formula to extract each letter from the word. By using the ROW function, we can easily copy the formula down and get all the letters in a single column. But then comes the tricky part - counting how many times each letter occurs. We use the COUNTIF formula to check if each letter is equal to the letter in the original word and get the count. However, Ram wanted to see each letter only once, making the task a bit more challenging.

So, we turn to the PivotTable feature to get a unique list of letters and their corresponding counts. But, as I always say, PivotTables are not always the best solution. So, I came up with a big, ugly array formula that does the job. It uses the FIND function to check for the first occurrence of each letter and returns the letter if it is found in the corresponding row. But, this formula has its limitations and cannot be used with the COUNTIF formula.

To overcome this limitation, we use a SUMPRODUCT formula in an unusual way. By converting the TRUE and FALSE values from the FIND function into ones and zeros, we can get the desired count for each letter. Overall, this was a challenging question from Ram, but we were able to find a solution. If you have a better solution, feel free to share it with us.

Don't forget to tune in tomorrow for a Dueling podcast with Mike, where we will be discussing another interesting Excel problem. And if you have any Excel questions or problems, don't hesitate to reach out to us at [email protected]. Thanks for watching, and we'll see you next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction
(00:15) Using Formulas to Count Occurrences of Letters
(01:31) Unique Letter Count Challenge
(02:02) Array Formula Solution
(03:03) Using SUMPRODUCT for Unique Letter Count
(03:50) Request for Better Solutions
(04:00) Upcoming Dueling Podcast
(04:10) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Array formula
Break out letters
Count letters
COUNTIF function
Dueling podcast
Formulas
Macro/VBA solution
MID function
Pivot table
SUMPRODUCT function
Unique letters


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152764/


Ram asks how to split a word into individual letters, and then count how many times each letter appears. Episode 1148 shows you how.

More from this channel