Counting occurrences of a character in a spreadsheet

22Jul

by Best Excel Tutorial Data Analysis

In this Excel tutorial you will teach yourself how to count occurences of a character in a spreadsheet.

Table of Contents

How to count occurrences of character in a string?

Let’s count how many of f character do you have in a A coulmn.

To do that just use below formula:

{=SUM(LEN(A:A)-LEN(SUBSTITUTE(A:A;”f”;””)))}

This is an array formula so remember to press CTRL + SHIFT + ENTER combination.

Counting character occurences

Counting occurrences of a character in a spreadsheet may be useful for spreadsheets with unique text.

Also you may be interested in counting character which is related to your company/project and used as a your internal code.

In this example you will see how to count the number of cells with specific content in a range in clever way. You need only one formula to do this!

To count a number of cells with a text in a specific range you just need to use sumproduct function.

You can build formula like this:

=SUMPRODUCT(–ISTEXT(RANGE))

You can count a number of cells with a value as well with similar formula:

=SUMPRODUCT(–ISNUMBER(RANGE))

You can use SUMPRODUCT with other logical functions to count cells based on various criteria:

ISBLANK(RANGE): Counts empty cells.
ISERR(RANGE): Counts cells with any type of error (e.g., #DIV/0!, #N/A).
ISERROR(RANGE): Counts cells with specific errors (e.g., #N/A).
ISEVEN(NUMBER): Counts even numbers.
ISFORMULA(RANGE): Counts cells containing formulas.
ISLOGICAL(RANGE): Counts cells containing logical values (TRUE or FALSE).
ISNA(RANGE): Counts cells containing the #N/A error.
ISNONTEXT(RANGE): Counts cells that do not contain text.
ISODD(NUMBER): Counts odd numbers.
ISOWEEKNUM(DATE): Counts the week number within the year for a given date.
ISPMT(RATE,PER,NPER,PV): Checks if a payment is due in a specific period for a given loan.
ISREF(RANGE): Checks if a cell contains a reference to another cell or range.

The are sooo many possibilities.