Only count cells with text, not formulas in Excel

Count Only Cells with Text, Excluding Formulas in Excel

Learn how to accurately count cells containing only text values, filtering out numbers, empty cells, and especially cells that display text but are actually the result of a formula in Excel.

Excel is a powerful tool for data analysis, but sometimes you need to count specific types of data. A common challenge is distinguishing between cells that contain static text and those that display text as the result of a formula. This article will guide you through various methods to precisely count only cells with actual text content, ignoring formulas, numbers, and empty cells. We'll explore solutions using Excel formulas and VBA.

Understanding the Challenge: Text vs. Formula-Driven Text

When you use a simple COUNTIF or COUNTA function, Excel often treats text generated by a formula (e.g., =IF(A1="Complete", "Done", "Pending")) the same as static text. This can lead to inaccurate counts if your goal is to identify cells where text was manually entered or imported as a literal string. The key is to differentiate between the displayed value and the underlying cell content.

Distinguishing between static text and formula-driven text in Excel.

Method 1: Using a Combination of Excel Formulas

Excel doesn't have a single built-in function to check if a cell contains a formula. However, you can combine several functions to achieve this. One effective approach involves using SUMPRODUCT, ISTEXT, and checking for the presence of a formula using a helper column or a more complex array formula.

For a simpler, non-volatile approach without a helper column, we can leverage GET.CELL (an old XLM macro function) within a named range, or for modern Excel, a combination of ISFORMULA (available in newer Excel versions) and ISTEXT.

Using ISFORMULA (Excel 365 / Excel 2013+)

If you have a newer version of Excel (Excel 365, Excel 2013, or later), the ISFORMULA function simplifies this task significantly. You can combine it with ISTEXT and SUMPRODUCT to count cells that are text AND are NOT formulas.

=SUMPRODUCT(--(ISTEXT(A1:A10)),--(NOT(ISFORMULA(A1:A10))))

This array formula (no Ctrl+Shift+Enter needed in newer Excel) counts cells in A1:A10 that contain text and are not formulas.

Let's break down this formula:

1. Step 1

ISTEXT(A1:A10): This part returns an array of TRUE/FALSE values, indicating whether each cell in the range A1:A10 contains text.

2. Step 2

ISFORMULA(A1:A10): This returns an array of TRUE/FALSE values, indicating whether each cell in the range A1:A10 contains a formula.

3. Step 3

NOT(ISFORMULA(A1:A10)): This negates the ISFORMULA array, so it returns TRUE for cells that do NOT contain a formula.

4. Step 4

--(...): The double unary operator converts TRUE/FALSE values to 1/0, respectively.

5. Step 5

SUMPRODUCT(...): This multiplies the two resulting arrays (text cells * non-formula cells) and sums the products. Only cells that are both text AND not a formula will result in 1*1=1, contributing to the total count.

Method 2: Using VBA (Visual Basic for Applications)

For older Excel versions or for more complex scenarios, VBA provides a robust solution. You can create a custom User-Defined Function (UDF) that checks each cell's content and formula status.

Function CountTextNoFormulas(rng As Range)
    Dim cell As Range
    Dim count As Long
    count = 0
    For Each cell In rng
        If cell.HasFormula = False And IsText(cell.Value) Then
            count = count + 1
        End If
    Next cell
    CountTextNoFormulas = count
End Function

This VBA function iterates through a range and counts cells that contain text and do not have a formula.

To use this VBA function:

1. Step 1

Press Alt + F11 to open the VBA editor.

2. Step 2

In the VBA editor, go to Insert > Module.

3. Step 3

Paste the VBA code into the new module.

4. Step 4

Close the VBA editor.

5. Step 5

Now, you can use =CountTextNoFormulas(A1:A10) directly in your Excel worksheet, just like any other Excel function.