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 bill@mrexcel.com. Thanks for watching, and we'll see you next time for another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: 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 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.
- Excel - How to Count Letter Occurrences in Excel using Formulas | Excel Tutorial - Episode 1148 ( Download)
- Excel - Dueling Excel: VBA vs. Excel 4 - Evaluating Text in Excel - Episode 1149 ( Download)
- Excel - Dueling Excel: VLOOKUP with Unsorted Data - Two Solutions - Episode 1144 ( Download)
- Excel - For School ADM Calculation - Does This Date Range Contain a Certain Date - Episode 1133 ( Download)
- Excel - Create a Floating Quartile Chart in Excel with Formulas & Charting Techniques - Episode 1143 ( Download)
- Excel - Which Rows Contain the Match - Dueling Excel - Episode 1139 ( Download)
- Excel - Excel Pie Chart Label Color Bug & Possible Solutions - Episode 1147 ( Download)
- Excel - Power of Custom Number Formatting in Excel | Learn to Use All 56 Colors! - Episode 1142 ( Download)
- excel tutorial on count if.avi ( Download)
- Excel - Extract the Right Two Letters in Excel - Episode 1158 ( Download)
- Excel for Math Classes: Counting in Other Bases II ( Download)
- Splitting Cell Text and Numbers Using Formulas And Text to Columns ( Download)
- Excel - Sort Excel Data Where Each Record Occupies Three Rows - Episode 1176 ( Download)
- Excel - How Separate First And Last Name In Excel - Episode 1145 ( Download)
- How to fill cells with similar information with a quick function | Excel 2007 ( Download)
