Counting Cells with Text in Excel (3 Easy Formulas)

Different times call for different measures because counting and totaling are going to be a substantial part of your Excel journey so you can get to know how to do it right. The how to do it different part comes when counting text-specific cells or counting without the blank cells. Use the SUMPRODUCT, ISTEXT, COUNTIF, and EXACT functions or a VBA code to make the counting count and to get the task done.

The text in your data represents a value and you want to see how many times that value appears in the data. You want to search the column by a specific word. The search should overlook blank cells. The count should only include case-sensitive matches. If any of these examples are why you're here, gather around for today's lesson on counting cells with some type of text.

Let's get counting!

Method #1 – Using SUMPRODUCT and ISTEXT Function

We'll begin with counting text cells using the SUMPRODUCT and ISTEXT functions. The SUMPRODUCT function sums the products of corresponding ranges like (A1xB1) + (A2xB2)… The ISTEXT function returns TRUE for a value that is text and FALSE otherwise. How does this marry up to count cells you ask? ISTEXT searches the range for text cells and splays its results which are then collated by the SUMPRODUCT function.

What a good story. Let's put it into motion. See the example shot here, the type of data therein and what type qualifies as text and what doesn't:

That was a breakdown for you so you know why a cell will be considered to contain text. Snapping back to the objective of today, let's clear the breakdown away and start counting the text cells, thankfully not manually. Use the SUMPRODUCT and ISTEXT functions in your formula to count the cells with text.

The range B3:B26 is checked for text cells with the ISTEXT function. On its own, ISTEXT spills the result for each cell, just like what we've shown you in the example shot above, returning the list of TRUEs and FALSEs. The double unary before ISTEXT converts TRUE and FALSE into 1s and 0s respectively. This makes it easier for the SUMPRODUCT function to add all the 1s and 0s up and give the final figure as 9. This means that there are 9 cells with text in our case example.

You can confirm this count from the example shot if your manual instincts are taking over.

Method #2 – Using COUNTIF Function

Get the COUNTIF function onboard to count text cells on your worksheet. The COUNTIF function counts the number of cells in a range that meet the specified condition. So, what's that condition going to be? The condition is that the cell must contain text. You've already seen how to do that earlier and if you're attempting it with the COUNTIF function, here's the formula to count text cells:

The range given to the COUNTIF function is B3:B26 which contains the data. The counting criterion is "*" – an asterisk enclosed in double quotes. The asterisk is used here as a wildcard character and represents any number of characters including zero characters. The indication is to count the cells that contain whatever number of characters and characters indicates text here. Therefore, the COUNTIF function has also counted 9 cells containing text in B3:B26.

There's more to the COUNTIF function than what has met the eye until now and we can use it for counting text cells in different scenarios like counting excluding blank cells and cells with specific or partial text. Read on below!

Excluding Blanks While Counting

Now let's use the COUNTIF function to count text cells excluding blanks. While blank cells themselves won't make it to the count, a formula returning an empty string (i.e. "") will be considered a text cell. So if you're counting cells that include text and empty strings, all of them would qualify as text cells like in our example case below.

We have some employee-specific sales data and have entered a formula to return a checkmark symbol if the sales figure is greater than the target figure. In that case, the employee would be eligible for a bonus. If not, we get an empty string. To count the text cells keeping the blank cells out, try this formula with the COUNTIF function:

We are searching column D for text cells, to know how many employees have earned their monthly bonus. To ignore the blank cells, we have used the condition "?*". The question mark is also used as a wildcard character denoting any single character.

Blank cells are devoid of any characters so the expression "?*" pushes the condition that the cell must contain at least one character to be counted. Therefore, the blanks are left behind and the rest of the text cells are counted and returned as 5:

Count Cells with Specific Text

Next, the COUNTIF function will look for the cells containing user-specified text aka the full match. In another case example, we are checking how many haircuts or trims have been performed in the day. To count the haircuts/trims, we can instruct the COUNTIF function through this formula:

The COUNTIF function is to only count the cells that have the text "haircut/trim" in the range B3:B24. This results in 6 cells with the text matching completely.

Note that "haircut/trim" works with the COUNTIF function for picking up on "Haircut/trim" in the data which is in capital initials. This means that the COUNTIF function is case insensitive. Later we will talk about conducting a case-sensitive count.

Partial Match

What if you have a long list to leaf through and can't pinpoint the complete content of the cell you want to search for? Or the part that you want to search for can be in more than one type of matching data. E.g. if we had another category "Haircut", we would want both types of cells counted; the ones that contain "Haircut/Trim" and "Haircut". To use the COUNTIF function to count the cells containing partially matching text, see the following formula:

The search range is the same and the criterion has been changed to counting cells that contain "*haircut*". The wildcard asterisks specify that the cells to be included in the count can also contain any other text but must include "haircut". We are given 6 cells as the count:

Case Sensitive Match

We promised a case-sensitive search and count so here we go. We will be exacting our search with the EXACT function. How wholesome of us. The EXACT function checks two text strings for exact similarity, also taking care of case sensitivity. For a count, we will use the SUMPRODUCT function like so:

The EXACT function checks B3:B24 for exact matches with the text "Haircut/Trim" and spills the result into TRUE for identical text and FALSE otherwise. TRUE and FALSE are changed into 1 and 0 with the double negative signs. Finally, the SUMPRODUCT function puts all the 1s together to give us the count of 6 perfectly matched cells.

Method #3 – Using VBA

Having used a few functions, we can give codes a go using VBA to count cells with text in Excel. VBA will program the count for us, and we can choose to receive the result in a message box, as you will see that we have done in this case. The code you will require for VBA to perform the text cell count is given in the instructions below.

• Select the cells where you want to conduct the count.
• Press the Alt + F11 keys to open the Visual Basic You can also do this using the Developer tab if you have enabled it. Click on the Visual Basic icon.
• 
• In the editor, click on Insert from the toolbar and select Module to gain access to a Module
• 
• Copy and paste the given code into the Module

• Sub CountTextCells()
  Dim txtCount As Long
  txtCount = Application.WorksheetFunction.CountIf(Selection, "*")
  MsgBox "Found " & txtCount & " text value(s)"
  End Sub

  The code is centered around performing a text count which you can see is defined by CountIf in the third line of the code. The range will be whatever is selected (as per "Selection" in the code) and "*" indicates that the counted cell must include zero or more characters.
  This is the same as we have seen with the COUNTIF function earlier so you can change the condition in the code according to the type of count the code is being made for (e.g. "?*" in the code instead of "*" will exclude blank cells from the count).
  From the code, we can deduce that the Message Box will appear with the text enclosed in double quotes with the result of the text count in between.

• As highlighted above, hit the Run button from the toolbar or press the F5 key to run the code.

Behold your Message Box with the specified count of text cells in the selection:

Notes:

The OK button on the Message Box leads back to the Module window with the code.

This code works on any open worksheet and any selection made on the sheet.

Counting cells with text in Excel should be simple for your work now as we detailed all the easy ways in this tutorial. We're fine-tuning the details on more Excelness while today's guide keeps you occupied. Be back in time for more because more is good when it comes to Excel tricks!