Look Who it Was Random video

Excel - Excel Tutorial: How to Count Substrings in Excel Using a Three-Step Solution - Episode 497

327 views · Published 21 April 2009 · 2:40 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Count Substrings in Excel Using a Three-Step Solution.

Welcome back to another MrExcel netcast! In this video, we'll be tackling a question that came up at one of my Excel seminars and was also sent in by a viewer named George. It's a common issue that many Excel users face - how to count the number of times a certain value occurs in a dataset. But what happens when you're not looking for an exact match, but rather a substring within a cell? That's where things get a bit tricky.

In this dataset, we have various values in Column B and we want to count how many times a certain value occurs. Normally, we would use the COUNTIF function, but in this case, it only counts exact matches. So, if we look for the value "ABC", it will only count cells that contain "ABC" and not cells that contain "XYZ, ABC". This is where our three-step solution comes in.

The first step is to create a new cell with the value we're looking for. Then, we add a new column to our dataset called "there" and use the FIND function to check if the value is present in each cell. This will either return a number or an error. Next, we use the ISERROR function to convert the results to either true or false, and then use the NOT function to reverse the results. This way, the "there" column will only show true if the value is found in the cell.

Finally, we can use the COUNTIF function again, but this time, we'll use the "there" column as our range and count how many cells have a value of true. This will give us the total number of cells that contain our desired substring. It may seem like a lot of steps, but it's a clever workaround to a common problem.

I hope this video was helpful in showing you how to find substrings in Excel. Don't forget to subscribe to our channel for more Excel tips and tricks, and leave a comment below if you have any questions or suggestions for future videos. 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 and question
(00:25) Using COUNTIF function
(00:37) Limitations of COUNTIF for substrings
(01:00) Three-step solution
(01:10) Step 1: Create new cell with value
(01:22) Step 2: Add new column with FIND function
(01:52) Step 3: Use NOT function and COUNTIF to find substrings
(02:14) 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:
Counting occurrences in Excel
Excel COUNTIF function
Excel COUNTIF with boolean values
Excel data analysis tutorial
Excel FIND function
Excel ISERROR function
Excel tips and tricks
How to count substrings in Excel
Reversing boolean values in Excel
Substring search in Excel
Three-step process for counting values in Excel

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



George sends in a question  How can COUNTIF be made to look for cells that match a pattern? Episode 497 shows the long workaround to this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel