Counting occurrences of a character in a spreadsheet

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

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.

How to count cells with a specific content in a range?

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

Other examples

There are others functions you can use with this kind of formulas:

  • =SUMPRODUCT(–ISBLANK(RANGE))
  • =SUMPRODUCT(–ISERR(RANGE))
  • =SUMPRODUCT(–ISERROR(RANGE))
  • =SUMPRODUCT(–ISEVEN(NUMBER))
  • =SUMPRODUCT(–ISFORMULA(RANGE))
  • =SUMPRODUCT(–ISLOGICAL(RANGE))
  • =SUMPRODUCT(–ISNA(RANGE))
  • =SUMPRODUCT(–ISNONTEXT(RANGE))
  • =SUMPRODUCT(–ISODD(NUMBER))
  • =SUMPRODUCT(–ISOWEEKNUM(DATE))
  • =SUMPRODUCT(–ISPMT(RATE,PER,NPER,PV))
  • =SUMPRODUCT(–ISREF(RANGE))

How to count occurrences using Vba?

In Excel VBA, you can count the number of occurrences of a certain value in a range using the following code:

See also  How to Copy a Table from Excel to Word

Function CountOccurrences(rng As Range, value As Variant) As Long CountOccurrences = WorksheetFunction.CountIf(rng, value) End Function

You can then use this function in your VBA code by passing in the range you want to search and the value you want to count occurrences of. For example:

Sub ExampleCountOccurrences()

  Dim rng As Range

  Set rng = Range("A1:A10") ' Define the range you want to search

  Dim value As Variant

  value = "apple" ' Define the value you want to count

  Dim count As Long

  count = CountOccurrences(rng, value)

MsgBox count & " occurrences of " & value & " found in range " & rng.Address

End Sub

This code will return the number of occurrences of the value “apple” in the range “A1:A10”.

Isn’t an Excel wonderful that makes your life so much easier?