How to Count Text or Number in Excel

In this article we will be looking into examples to count words or numbers in Excel using different formulas to return the count of Excel cell.

While doing data analysis on an excel spreadsheet or building an Excel dashboard, we get into a situation where you are looking to extract essential information from a data set. Counting letters, numbers, and blank is part of the analysis.

Download Your Example Excel File

  • While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

Count only numbers in Excel

In our first Example, we will try to count the only number excluding text, symbols or any other characters.

In our data set, you have five entry and one blank cell. Our assignment is to find only those numbers that will exclude any other listing in a given range.

For this, you will be using the COUNT function. The COUNT function count of numbers within the reference list of arguments ignoring text & blank cells (only numeric).

Use COUNT function to find the count of numeric values
Use COUNT function to find the count of numeric values

The return value is the count of numbers only, you can notice it does not count blank cells.

Count only Text in Excel

In our second assignment, our priority is to find only Text from a set of the data range. The important thing to notice is that the data range includes numbers, blank and symbols.

To find the text count only you will use the COUNTIF function. The COUNTIF function returns a count of number based on given criteria like

=COUNTIF(A1:A5,”>5″)

that search numbers in the list >5 and return the count of numbers that are greater than 5.

Use Wildcard character asterisk (*) as criteria to find number of texts
Use Wildcard character asterisk (*) as criteria to find number of texts

Excel can search any number of characters or any single character using wildcard characters like like ‘?,*,~’ for advanced search and partial match.

Count only Text using SUMPRODUCT

In this example, we will count Text in the data range using the SUMPRODUCT and ISTEXT function together.

Moreover, you will understand an exciting way of counting data using a double hyphen or two negative sign (–).

The next Formula to count text is

=SUMPRODUCT(- –ISTEXT(B5:B10))

Double Hyphen converts the True and False to 1 and 0 respectively.

The Excel SUMPRODUCT function returns a sum of the product of numbers together distributed in single or multiple range or arrays, an easier way to add multiple integers.

The Excel ISTEXT function checks if a specified cell has a value in text format and returns “TRUE” when a cell contains a text value, and “FALSE” if not.

SUMPRODUCT count 1 (True) converted using double hyphen (--)
SUMPRODUCT count 1 (True) converted using double hyphen

There are multiple ways of counting text or number on Excel. We can also count color cell or unique text using different approach.

For last example, in place of SUMPRODUCT function you can use SUM as an Array formula to calculate the count of text in this case.

{=SUM(–ISTEXT(B5:B10))}

The SUM function returns a number after adding multiple numeric arguments together. 

SUM as Array function replace SUMPRODUCT
SUM as an array function can replace SUMPRODUCT function
  • If SUMPRODUCT does not have two range the function simply add number in the range.
  • Considering COUNTIF is not case sensitive SUMPRODUCT function can be an alternative.