Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Frequency Function Example
  • Get column index in Excel Table
  • Data Series in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Excel Pie Chart

References

  • How to use Excel FORMULATEXT function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel LOOKUP Function

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation date in specific year
  • Excel Data validation must contain specific text

How to count specific characters in a range in Excel

by

If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function.

Formula

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,text,"")))

Explanation

In the example, the active cell contains this formula:

=SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,"o","")))

How the formula works

For each cell in the range, SUBSTITUTE removes all the o’s from the text, then LEN calculates the length of the text without o’s. This number is then subtracted from the length of the text with o’s.

Because we are using SUMPRODUCT, the result of all this calculation is a list of items (an array), where there is one item per cell in the range, and each item a number based on the calculation described above. In other words, we have a list of character counts, with one character count per cell.

SUMPRODUCT then sums the numbers in this list and returns a total for all cells in the range.

SUBSTITUTE is a case-sensitive function, so it will match case when performing a substitution. If you need to count both upper and lower case occurrences of a specific character, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before the substitution occurs. Then supply an uppercase character for the text that’s being counted.

The modified formula looks like this:

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(UPPER(range),text,"")))

Post navigation

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use IFS function in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel NOT Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • How to calculate future date say 6 months ahead in Excel
  • Calculate total hours that fall between two times in Excel
  • How to Calculate Age in Excel
  • Calculate number of hours between two times in Excel
  • Convert date string to date time in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel

General

  • How to count total number of cells in a rectangular range in Excel
  • Excel Default Templates
  • How to calculate project complete percentage in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Using Existing Templates in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning