How to count occurrences of characters and numbers in Excel

How to count occurrences of characters and numbers in Excel

Depending on your needs, it may feel time-saving and convenient to count how many occurrences of given characters happen in an excel spreadsheet. Here we show you how to count occurrences of characters in a range of cells, or in a single cell.

How to count occurrences of the same value in cells?

Excel provides a built-in function called COUNTIF, which is described as:

  • =COUNTIF(Where do you want to look?, What do you want to look for?)

Let's say you have a column from C1 to C500 full of different numbers in each, and you would like to count the occurrences of the number 1. In a separated cell, you will type the following formula:

  • =COUNTIF(C1:C500,"1")

How to count occurrences of a text string in one cell?

If you want to count how many occurrences of the same character happen in one cell, you can use the following formula:

  • =LEN(Cell index)-LEN(SUBSTITUTE(Cell index,"what you are counting",""))

For example, if your cell E1 is a string and you want to count how many 1 character happens in "1111555", type:

  • =LEN(E1)-LEN(SUBSTITUTE(E1,"1",""))

How to count occurrences of a text string in a range of cell?

If you want to count how many occurrences of the same character happen in one cell, you can use the following formula:

  • =LEN(Cell range)-LEN(SUBSTITUTE(Cell range,"what you are counting",""))

For example, if your range E1:G5 contains strings and you want to count how many 1 character happens in it, type:

  • =LEN(E1:G5)-LEN(SUBSTITUTE(E1:G5,"1",""))
any more questions about excel? check out our forum!

Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11

Excel