How to count / sum strikethrough cells in Excel?

In Excel, it is common to apply strikethrough formatting to certain cells in order to mark data as outdated, irrelevant, or invalid. This visual cue can greatly improve data management and help you focus on the information that matters during analysis. However, when it comes to performing calculations—such as counting or summing—you may want to separate the results for strikethrough cells from those without strikethrough. Understanding how to handle these scenarios can streamline your workflow and lead to more accurate insights. This article provides several practical methods to count and sum cells with or without strikethrough formatting in Excel, using customizable VBA functions that offer flexibility for varied data management needs.

Count strikethrough cells with VBA in Excel

Count without strikethrough cells with VBA in Excel

Sum exclude strikethrough cells with VBA in Excel

Count or Sum Cells with Strikethrough Using Kutools for Excel

Count strikethrough cells with in Excel

If you need to determine the total number of cells that have been formatted with strikethrough in a specific range, this can’t be achieved directly with Excel's built-in functions, since there’s no formula that recognizes text formatting like strikethrough. Therefore, using a User Defined Function (UDF) in VBA becomes a practical solution. This approach is especially useful when managing to-do lists, inventory updates, or any scenario where strikethrough is used to indicate completed, invalid, or obsolete data points.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the VBA window, click Insert > Module to create a new module, and then paste the following code into the Module Window.

VBA code: Count strikethrough cells

Public Function CountStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
    If pRng.Font.Strikethrough Then
        xOut = xOut + 1
    End If
Next
CountStrike = xOut
End Function

3. After entering the code, save your workbook (to retain the macro), then close the VBA editor and return to your worksheet. In any blank cell where you want the result to appear, enter the following formula (for example, to check strikethrough cells in the range A2:B14): =CountStrike(A2:B14)

Tip: You can change A2:B14 to any other range that you’d like to check.

4. Press the Enter key to execute the formula, and Excel will immediately display the count of all strikethrough cells in the specified range. This allows you to efficiently track progress or remove deprecated entries as needed. See screenshot:

Precaution: To ensure this VBA code remains available for future use, you must save the file in .xlsm (Excel Macro-Enabled Workbook) format.

Count without strikethrough cells with in Excel

There are cases where you may require the count of only those cells that do not have the strikethrough formatting in a given range. For instance, in project tracking tables or sales data, you often wish to count only the active/valid entries (i.e., those without strikethrough) for further operations. Excel’s default COUNT or COUNTA functions disregard text formatting, so a tailored VBA solution offers precise control over such needs.

1. Press ALT + F11 to launch the Microsoft Visual Basic for Applications window.

2. Go to Insert > Module to add a new code module. Paste the following code into that module window.

VBA code: Count without strikethrough cells

Public Function CountNoStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
    If Not pRng.Font.Strikethrough Then
        xOut = xOut + 1
    End If
Next
CountNoStrike = xOut
End Function

3. Save your macro-enabled workbook, return to the worksheet, and in a blank cell enter the formula (for example, for A2:B14): =countnostrike(A2:B14) Then, press Enter to get the result. Excel will count just those cells within the selected range that do not have strikethrough formatting.

Note: In the above example, A2:B14 is the range of cells being evaluated. Adjust the range reference as needed for your worksheet. Also, be aware that merged cells or special formatting may sometimes affect the results—double-check that your range contains only the intended cells.

Sum exclude strikethrough cells with in Excel

When dealing with numeric data where certain entries are no longer active or should be ignored (i.e., have strikethrough formatting), it’s often essential to sum only the valid numbers. This is common in budget sheets, inventory management, or task tracking, where strikethrough entries represent obsolete or completed items that must be excluded from the totals. Since Excel's built-in SUM function does not distinguish between formatted and non-formatted cells, a User Defined VBA Function is needed for this operation.

1. Use ALT + F11 to open the Microsoft Visual Basic for Applications editor.

2. Click Insert > Module to add a module, and paste the code below into that module window.

VBA code: Sum exclude strikethrough cells

Public Function ExcStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
    If Not pRng.Font.Strikethrough Then
        xOut = xOut + pRng.Value
    End If
Next
ExcStrike = xOut
End Function

3. Save and close the VBA editor. In your target worksheet, choose a blank cell and enter the following formula (e.g., for B2:B14): =excstrike(B2:B14) Press Enter, and Excel will sum only the cells in your specified range that do not have strikethrough formatting, instantly providing a sum that excludes obsolete values.

Note: Adjust B2:B14 to your actual data range. This function ignores strikethrough cells no matter their value. If you experience unexpected results, ensure all cells have numeric entries and the correct formatting.

Practical tip: If you need to refresh the summation after changing any cell’s strikethrough status, recalculate the worksheet by pressing F9.

Count or Sum Cells with Strikethrough Using Kutools for Excel

If you want to count or sum only the cells that have a strikethrough format, Excel doesn’t offer a built-in way to do this—but Kutools for Excel makes it easy with its Special Filter feature.

1. Select the Column

Click the column that contains the cells with strikethrough formatting.

2. Open the Special Filter

Go to the Kutools Plus tab, then click Special Filter > Filter Strikethrough.

3. Confirm the Filter

A dialog box will appear showing how many cells matched the condition. Click OK to continue.

4. View the Results

The cells with a strikethrough are now filtered and visible. Select the filtered range—you’ll instantly see the Sum, Average, and Count in Excel’s status bar at the bottom.

✨ Why Use Kutools?

Kutools for Excel offers over 300 powerful and easy-to-use features just like this one—helping you save time and boost productivity without writing complex formulas or VBA code.

Whether you're filtering by format, cleaning up data, combining sheets, or batch editing, Kutools simplifies your work in seconds.

 Try Kutools for Excel free for 30 days and explore what you've been missing!

Related articles:

How to sum / count bold numbers in a range of cells in Excel?

How to count and sum cells based on background color in Excel?

How to count / sum cells based on the font colors in Excel?