Count Cells With Any Text – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 9, 2023
Download Example Workbook

Download the example workbook

In this tutorial, we will use the COUNTIF and SUMPRODUCT functions to count cells containing any text within a range.

count cells with any text main Function

COUNTIF To Count Cells With Any Text

The COUNTIF function counts cells that meet certain criteria. In this case, we want to count cells that contain any text. We do this by using an asterisk wildcard symbol:

=COUNTIF(B2:B6,"*")

COUNTIF

 

The asterisk wildcard matches any string of text of any length. If you have no characters before or after the asterisk (as in our example above), it will match any and all text.

Remember when entering text strings into formulas, you must always surround the strings with quotations marks. This is true even of wildcards.

COUNTIFS Function to Count Cells with Conditions

The COUNTIFS function works the same as the COUNTIF function, except it allows for multiple criteria. To demonstrate an example, let’s use the same example as above except this time we will exclude cells that say “ABC”.  We can do this by adding the “<>” symbol as a conditional statement which represents “not equal to”.

=COUNTIFS(Range,"*", Range,"<>Text")
=COUNTIFS(B2:B6,"*", B2:B6, "<>ABC")

COUNTIF EXCLUDE

You will notice that even in this formula, the condition is surrounded by quotation marks. You can add as many conditions as you require, just remember to follow the function’s formula structure by adding the range and then the condition.

It should be noted that “<>ABC” is not case sensitive so it will also include cells that are lower case, i.e. cells with “abc”, “ABC”, “aBc” will all be included.

SUMPRODUCT To Count Cells With Any Text

The SUMPRODUCT function can also be used to conditionally count cells. It’s more complicated than the COUNTIF function, but is much more powerful.

=SUMPRODUCT(INT(ISTEXT(Range))
=SUMPRODUCT(INT(ISTEXT(B2:B6))

Sumproduct

Let’s break down this formula to understand each part of it

Highlight a portion of the function in the formula bar and press F9 to see the calculated value of that formula portion.

ISTEXT is a Boolean function that returns TRUE or FALSE based on whether a cell contains text. However, when used inside a SUMPRODUCT Function it will return an array of TRUE/FALSE values: {TRUE; FALSE; FALSE; TRUE; TRUE}.

sumproduct is text count any cells 2

Next we use the INT Function to convert the Boolean values to 1s and 0s so that the SUMPRODUCT function can sum the values, counting the number of cells containing text.

sumproduct is text count any cells text 3

sumproduct is text count any cells text 4

You can also use the double unary sign “–” (also commonly referred to as the double negative) which converts TRUE and FALSE values into 1s and 0s respectively.

sumproduct double unary count any cells with text 1

sumproduct double unary count any cells with text 2

Last, SUMPRODUCT will take the summation of that array: 1 + 0 + 0 + 1 + 1 = 3.

 

Google Sheets – Count Cells With Any Text

All of the above examples work exactly the same in Google Sheets as in Excel.

Count Cells With Any Text Google Sheet

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List