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",""))