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 cells.
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 entries and one blank cell. Our assignment is to find only those numbers that will exclude any other listing in a given range.
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 numbers based on given criteria like
that search numbers in the list >5 and return the count of numbers that are greater than 5.
Excel can search any number of characters or any single character using wildcard characters like ‘?,*,~’ for advanced search and partial match.
Count only Text using SUMPRODUCT
Moreover, you will understand an exciting way of counting data using a double hyphen or two negative signs (–).
The next Formula to count text is
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 a 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.
There are multiple ways of counting text or numbers on Excel. We can also count color cells or unique text using a different approaches.
For the last example, in place of the SUMPRODUCT function, you can use SUM as an Array formula to calculate the count of text in this case.
The SUM function returns a number after adding multiple numeric arguments together.
- 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.