Excel COUNTIF for Red Text: Methods That Actually Work

Formulas & functions · Updated November 19, 2026

COUNTIF cannot directly count cells by red font color: it evaluates cell values, not formatting. To count red text in Excel, count the rule or data condition that makes it red, or use a VBA function for cells with manually applied font color.

Why COUNTIF cannot read text color

Excel worksheet functions such as COUNTIF and COUNTIFS compare values against criteria: text, numbers, dates, and conditions such as greater than or equal to a value. Font color is formatting metadata, not part of the cell value, so a criterion like “red” is not available in COUNTIF.

This distinction matters because two cells can contain the same word but have different font colors, or have different values with the same red font. A regular count function sees the values and ignores the visual formatting. Changing a cell’s font color also does not normally trigger recalculation of formulas that depend on its value.

Excel has no built-in worksheet function named COUNTIFCOLOR or a color criterion for COUNTIF. If a solution claims that you can simply add “red” as a COUNTIF criterion, it is either relying on a separate helper method or misrepresenting what the function can do.

First distinguish red font from red fill

“Red text” usually means red font, but a red cell background is a different property. Excel’s filter-by-color feature and VBA code can target font color or fill color separately. Make sure you select font color when that is what you need; counting red-filled cells will not necessarily count red lettering.

Also find out how the red appearance was created. It may be:

  • Direct formatting: someone selected the cells and set the font color to red.
  • Conditional formatting: Excel displays the font in red when a rule is true, while the underlying font setting may be different.
  • A theme or custom color: the font may look red without using the exact standard red value you expected.

The right counting method depends on this distinction. If red indicates a meaningful condition, such as overdue status, counting that condition is usually more dependable than counting the appearance.

Best method: count the condition behind the red formatting

If a conditional-formatting rule turns text red because of a value or status, reproduce that rule with a normal count formula. This counts the underlying records, remains useful when the formatting changes, and does not require macros.

For example, imagine due dates are in C2:C6 and payment statuses are in D2:D6. Suppose the red font is applied to rows that are overdue and not marked Paid. With the illustrative dates 1 May 2025, 2 May 2025, 1 January 2027, 1 June 2025, and 1 July 2025 in C2:C6, and statuses Open, Paid, Open, Open, and Paid in D2:D6, the matching rows as of 3 November 2026 are rows 2 and 5 only if the date/status pairings follow those listed positions? Check each pair: row 2 is overdue and Open; row 3 is overdue but Paid; row 4 is in the future and Open; row 5 is overdue and Open; row 6 is overdue but Paid. For a five-row example, use the first five entries only: rows 2 and 5 match, so the count is 2.

With due dates in C2:C6 and corresponding statuses in D2:D6, use this formula to count overdue rows that are not marked Paid:

=COUNTIFS(C2:C6,"<"&TODAY(),D2:D6,"<>Paid")

With the five-row example above and the current date set to 3 November 2026, this returns 2: rows 2 and 5 are before today and have Open status. Keep the actual conditional-formatting rule and the count criteria aligned; if the rule also checks a blank field or a particular category, include that same condition in the count.

Count manually colored font with a VBA function

When the red font was applied manually and you need a count of the current formatting, a small VBA user-defined function can compare each cell’s font color with a sample cell. This is a macro-based solution, not a built-in Excel function.

On Windows, open the Visual Basic Editor from Developer > Visual Basic, choose Insert > Module, and put the function below in the module. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. In Excel for Mac, the Visual Basic Editor is available from Developer > Visual Basic when that tab is shown; depending on the Excel edition, it can also be opened from Tools > Macro > Visual Basic Editor.

Public Function CountFontColor(rng As Range, sampleCell As Range) As Long
    Dim cell As Range
    Dim targetColor As Long
    Dim total As Long

    targetColor = sampleCell.Font.Color
    For Each cell In rng.Cells
        If Not IsError(cell.Value2) Then
            If Len(CStr(cell.Value2)) > 0 Then
                If cell.Font.Color = targetColor Then
                    total = total + 1
                End If
            End If
        End If
    Next cell

    CountFontColor = total
End Function

With the cells to check in A2:A6 and a sample cell D1 whose font is set to the red you want to match, enter this formula in another cell:

=CountFontColor(A2:A6,$D$1)

If two nonblank cells in A2:A6 have the same direct font color as D1, the formula returns 2. The function skips blank cells and error values. It counts any nonblank value with that font color, including numbers; it is not limited to text strings. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and allow macros if you want the function to run.

Conditional formatting needs special care

A VBA function that reads cell.Font.Color sees the cell’s regular font formatting. It does not reliably represent the color currently displayed because of conditional formatting. That is why the value-based approach is preferable when the red appearance comes from a rule.

There is an additional limitation: Excel does not automatically recalculate a worksheet formula just because you changed a cell’s formatting. After editing direct font colors, a VBA color-count formula may continue to show its old result until a recalculation occurs. Pressing F9 (Fn+F9 on some Mac keyboards) requests calculation, but formatting changes do not always behave like changed cell values. If the count must update consistently after formatting edits, use a macro that performs the count when requested, or avoid using color as the stored data.

A macro that inspects the displayed result of conditional formatting is a different approach from the worksheet function above. It must be run as a VBA procedure rather than assumed to work as an ordinary formula. For a recurring report, reproducing the conditional rule in a count formula is generally simpler and less fragile.

Quick option: filter by font color

For a one-time count, filtering the list by font color may be the quickest route. Select the data range, choose Data > Filter, open the filter arrow for the relevant column, then choose Filter by Color and the red font color. The available color choices depend on colors present in that column. Check that you are filtering by font color, not cell fill.

After filtering, select only the data cells in the column you want to count. Excel’s status bar can show a Count for selected nonempty cells; if Count is not visible, right-click the status bar and enable it. Another option is to put a subtotal formula outside the filtered list. With the values to count in A2:A6 and the filter showing two nonblank matching records, enter:

=SUBTOTAL(103,A2:A6)

This returns 2 in that example. Function number 103 counts nonempty visible cells while excluding rows hidden by the filter and rows hidden manually. If the target range contains blanks, it counts visible nonblank cells, not every visible row.

Older alternatives and common misconceptions

You may encounter instructions using the old Excel 4.0 macro function GET.CELL in a defined name. It is not a normal worksheet function that you can type directly into a cell, and its color information can be less intuitive than a direct VBA comparison. Workbooks using older macro functions may also require macro-related security settings. For a new workbook, use a value-based formula, filtering, or a clearly documented VBA function instead.

Another common confusion is the worksheet function CELL. Its color-related information does not provide a general test for whether a font is red. It should not be treated as a built-in replacement for counting font colors. Likewise, copying red text into a helper column does not preserve an automatically countable “red” property unless you deliberately store a value or category that represents the reason for the color.

Choosing a reliable approach

Use the method that matches the job rather than treating the color as the data:

  • Use COUNTIF or COUNTIFS on the underlying condition when red communicates a status, threshold, date, or category. This is usually best for ongoing work.
  • Use Filter by Color when you need a quick manual count and do not need the result to update as a formula.
  • Use VBA when the font color itself is the only available information and the workbook can use macros.
  • Use a helper column when people apply colors manually but the workbook needs dependable reporting. Record a label such as Review, Late, or Priority and use that value for counts.

Color is helpful for scanning a sheet, but it is a poor substitute for a data field. A status column makes results easier to audit, filter, share, and use in charts, and it avoids ambiguity between direct formatting and conditional formatting.

Frequently Asked Questions

Can COUNTIF count red font in Excel?
No. COUNTIF evaluates values and criteria, not font formatting. Count the condition behind the color, filter by font color, or use VBA if you must count manually formatted cells.

Can I count cells that turn red because of conditional formatting?
The dependable option is to count the same values or conditions used by the conditional-formatting rule. A basic VBA function that checks Font.Color may not see the displayed conditional-formatting color.

Does the VBA function count red-filled cells too?
No. The example compares font color. Fill color is a separate formatting property and needs different VBA logic; Excel’s color filter also lets you choose between font and fill where those options are available.

Why did my color count not change after I recolored a cell?
Changing formatting does not necessarily trigger formula recalculation. Request a recalculation or rerun the counting procedure, and remember that a worksheet UDF may still be unsuitable for conditional-formatting colors.

Will the VBA solution work in Excel for the web?
Excel for the web cannot create, run, or edit VBA macros. Use the underlying-condition formula or filter-based method there, or open the workbook in desktop Excel to use VBA.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.