/ How to

How to Count and Tally Cells Containing Text in Excel Spreadsheets

Excel is a powerful spreadsheet program that allows you to store, organize, and analyze data. One common task is to count or tally the number of cells that contain text values in a range. This can be useful for summarizing and reporting on textual data in your spreadsheets.

As an Excel expert with over 10 years of experience, I will explain several methods to count cells with text using Excel’s built-in functions. I will also provide tips to handle different use cases you may encounter.

Using the COUNTIF Function

The easiest way to count cells containing text is using the COUNTIF function. COUNTIF allows you to count cells based on a criteria you specify.

Here is the basic syntax:

=COUNTIF(range, "*")

Where:

  • range is the range of cells you want to evaluate
  • * is a wildcard matching any text

For example, to count text values in cells A1 to A10, use this formula:

=COUNTIF(A1:A10,"*")

This will count all cells containing any text values, numbers entered as text, or formulas returning text.

Excluding Spaces from the Count

To exclude spaces, use an additional COUNTIF:

=COUNTIF(range,"*")-COUNTIF(range," ")

This counts all text values minus any cells containing just a space character.

Counting Text Cells in Filtered Data

To count text only in the visible cells after applying a filter, use the special range reference range.SpecialCells(xlCellTypeVisible):

=COUNTIF(range.SpecialCells(xlCellTypeVisible), "*")

This will count visible cells after filtering as you would expect.

Using SUMPRODUCT

Another approach is to use the SUMPRODUCT function together with the ISTEXT function:

=SUMPRODUCT(--ISTEXT(range))
  • ISTEXT returns TRUE/FALSE if each cell contains text
  • -- coerces TRUE/FALSE to 1/0
  • SUMPRODUCT sums the 1’s, ignoring the 0’s

This allows you to count text cells without needing to use wildcards.

Counting Specific Text

To count only cells with specific text, use COUNTIF with the text value instead of the wildcard.

For example, to count “Apples” only:

=COUNTIF(range,"Apples")

You can use * wildcards inside the text to match partial matches.

Counting Text Length

To count text cells based on the length of text, you can nest the LEN function inside COUNTIF.

For example, to count text cells containing over 5 characters:

=COUNTIF(range,LEN(range)>5)

This approach works for other numeric criteria as well.

Tips for Counting Text Cells

Here are some additional tips when counting text:

  • Sort your data first to group text cells together
  • Use conditional formatting to visually highlight text cells
  • Combine functions like COUNTIF/LEN to handle more complex criteria
  • Test different ranges during development to ensure you capture the right cells
  • Double check results against manual sampling to verify accuracy

VBA Macro Alternative

You can also use a VBA macro to count text cells if you need more flexibility:

Sub CountTextCells()

  Dim TextCount As Long
  Dim Cell As Range

  For Each Cell In Range("A1:A100")
    If Cell.Text <> "" Then
      TextCount = TextCount + 1
    End If 
  Next Cell

  MsgBox "There are " & TextCount & " text cells."

End Sub

This loops through each cell, checks if it contains text, and increments a counter if so.

The macro approach allows you to handle more complex logic compared to standard Excel functions.

Conclusion

Counting cells containing text is a common task in Excel. As you’ve learned, the COUNTIF function with a wildcard is the easiest method, but you have several options using other Excel functions too.

Pay attention to the exact criteria you want to match, test on a sample range during development, and validate your results to ensure accuracy when tallying text cells in your spreadsheets.

As an Excel expert and consultant, I hope these tips empower you to summarize and report on textual data more effectively. Let me know if you have any other questions!

Related Posts