Excel COUNTIF for Red Text: What Works

Formulas & functions · Updated November 19, 2026

Excel’s COUNTIF function cannot count cells by red font color: COUNTIF checks cell values, not formatting. To count red text, use a small VBA function for directly applied font color, filter the column by font color, or count the values or rule that cause the text to appear red.

Why COUNTIF does not see font color

COUNTIF evaluates the contents of cells against a criterion such as a word, number, or comparison. Font color is formatting, stored separately from a cell’s value, so a criterion such as “red” has no meaning to COUNTIF. The same limitation applies to SUMIF and most other ordinary worksheet functions: they do not use font color as a calculation condition.

For example, two cells may both contain the word Overdue while one is red and the other is black. COUNTIF treats them as matching values regardless of their appearance. Conversely, two cells may have identical red formatting but different contents; COUNTIF cannot identify their shared color.

For a normal value-based count, with status entries in B2:B100, the formula below counts cells containing the word Overdue, whether their font is red, black, or another color:

=COUNTIF(B2:B100,"Overdue")

COUNTIF is also not case-sensitive for ordinary text criteria. It can use wildcards, comparison operators, and cell references as criteria, but none of those options turns formatting into a criterion.

First identify what makes the text red

Before choosing a method, check how the red appearance was created. Select a red cell and inspect its formatting, or open Home > Conditional Formatting > Manage Rules to see whether a rule controls the color. This matters because directly applied font color and conditional-formatting color are not the same thing from a VBA function’s perspective.

  • Direct formatting: Someone selected the cell and chose a red font using Home > Font Color, or applied a style that sets the font color.
  • Conditional formatting: Excel changes the displayed font color when a value, formula, or other condition meets a rule. The cell’s displayed color can change as its contents or related cells change.
  • Value-based status: Red is simply a visual signal for a value such as Overdue, a negative number, or a priority label. In this case, counting the underlying value or condition is often the most reliable solution.

A cell can also look red because of a table style or another formatting rule. If you count color without checking its source, the result may not match what you see on the screen.

Best option when red is determined by a value

If a defined value or condition causes the red text, count that condition instead of the color. This avoids a formatting-dependent count and updates automatically when the cell values change. It is generally the best approach when color communicates a status rather than being an arbitrary choice.

For example, if the red text marks the word Overdue, and status entries are in B2:B100, use the same criterion that the formatting rule uses:

=COUNTIF(B2:B100,"Overdue")

If the rule colors numbers greater than 100 red, and the numbers are in B2:B100, count those values directly:

=COUNTIF(B2:B100,">100")

That second formula returns the number of entries above 100; for example, if 12 cells meet the condition, it returns 12. Check the actual conditional-formatting rule before adapting this idea. A rule may use a formula that refers to another column, multiple conditions, or a relative reference. In those cases, reproduce the rule’s logic rather than guessing from the color alone. For multiple conditions, COUNTIFS may be a better fit than COUNTIF.

Count directly applied font color with VBA

When cells have manually applied font colors and no value-based rule describes which ones are red, a VBA user-defined function can compare their font colors. The sample below counts nonempty cells in a range whose font color matches a sample cell. It checks the cell’s direct font formatting; it does not read the displayed color produced by conditional formatting.

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. If you use a Mac, the Visual Basic Editor is also available, although the keyboard shortcut can depend on the keyboard and function-key settings; use Excel’s Developer tab or Tools > Macro to open the editor if needed.

Public Function CountFontColor(ByVal dataRange As Range, ByVal colorSample As Range) As Long
    Dim cell As Range
    Dim wantedColor As Long

    Application.Volatile
    wantedColor = colorSample.Font.Color

    For Each cell In dataRange.Cells
        If Not IsError(cell.Value2) Then
            If Len(CStr(cell.Value2)) > 0 And cell.Font.Color = wantedColor Then
                CountFontColor = CountFontColor + 1
            End If
        End If
    Next cell
End Function

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only when you trust the file and its source. With values in A2:A100 and a sample cell D1 formatted with the target red font, enter this worksheet formula:

=CountFontColor(A2:A100,D1)

The function returns the number of nonempty cells in A2:A100 whose font color matches D1. For instance, if 7 cells match, the result is 7. D1 can be a blank cell; its font color is the color the function uses for comparison. A sample cell is useful because it avoids having to guess Excel’s internal color number.

Before running or adding VBA, save a separate copy of the workbook. Macros cannot be undone with Ctrl+Z, and macro-enabled files require appropriate security settings. The function also does not recalculate solely because you changed a cell’s font color: formatting changes do not normally trigger worksheet calculation. Press F9 to recalculate, or use Formulas > Calculate Now. Application.Volatile makes the function recalculate during calculation cycles, but it does not make a formatting edit itself trigger a calculation.

Conditional formatting needs special care

The VBA example reads cell.Font.Color, which represents direct font formatting. If a conditional-formatting rule makes text look red, the visible red may not be reflected by that property. A formula that appears to count red text can therefore return a result that disagrees with the displayed cells.

For conditionally formatted cells, the safer solution is usually to count the condition that drives the rule. If the rule is based on a status word or a numeric threshold, use COUNTIF or COUNTIFS for that status or threshold. If the rule depends on another column or a custom formula, translate that formula into an equivalent counting approach. When the rule has several interacting conditions, verify the logic against a few known rows before relying on the total.

Another practical option is to filter by the displayed font color and count the visible records. Excel’s filter menu can filter by color in supported ranges and tables. This can be useful for a one-time check, but it is an interactive filter rather than a color-aware COUNTIF formula.

Filter by font color for a quick count

To inspect or count matching records without writing VBA, turn on filters and choose the red font color in the column’s filter menu. In Microsoft 365 for Windows, select a cell in the data, choose Data > Filter, open the filter arrow for the relevant column, and select Filter by Color > Font Color. Choose the red shown in the list. The exact choices depend on the colors present in that column.

To get a formula count of the visible nonblank records, use an identifier or another column that is filled in for every record. With the filtered records’ identifiers in A2:A100, enter:

=SUBTOTAL(103,A2:A100)

SUBTOTAL with function number 103 counts nonblank visible cells and ignores rows hidden by a filter. If 7 filtered records have identifiers, the result is 7. Do not use a column with blanks if you want a count of records: blank identifiers would make the total too low. Clear the filter to see the full list again. This approach is convenient for review, but it depends on the filter currently applied and is not an independent count of every red cell in the workbook.

Common causes of incorrect counts

When a count seems wrong, first confirm that the selected range and the color source match what you intend to measure. These are frequent sources of confusion:

  • The color is conditional: A function reading direct font color may not match the color produced by a conditional-formatting rule.
  • Some colored cells are blank: A simple color loop might include blank cells. The VBA example above explicitly skips empty values.
  • The sample cell is not the same red: Similar-looking reds can be different font colors. Use a sample cell from the target cells when possible.
  • The function has not recalculated: Changing formatting alone may leave the displayed result unchanged until a calculation is requested.
  • The color is in a different column: Filtering or counting the wrong field can give a plausible but irrelevant result. Identify which column’s font color is meaningful.

Also distinguish font color from fill color. Red text is a font-color issue; a red cell background is a fill-color issue. The VBA sample compares font color, so it is not a count of red-filled cells. If the workbook uses colors inconsistently, consider adding a clear status column such as Overdue or Review needed and count that data instead.

Choosing the right method

Use the method that matches the task rather than treating every red cell as the same kind of data. A value-based formula is easiest to maintain when red indicates a rule. A color filter is useful for a quick visual review. VBA is appropriate when the color itself is the information and the cells are directly formatted.

  • For an automatically updating business count: Count the status, value, or criteria behind the color with COUNTIF or COUNTIFS.
  • For a one-off inspection: Filter by font color and use SUBTOTAL on a reliably populated identifier column.
  • For manually colored cells: Use the VBA function, account for recalculation, and keep the file macro-enabled.
  • For a shared workbook where macros are unsuitable: Replace color-only meaning with an explicit status value, then count that value.

A status column is often the most robust long-term design. Color can remain as a helpful visual cue, while the underlying text or code provides something formulas, filters, and other users can interpret consistently.

Frequently Asked Questions

Can COUNTIF count cells with red font?
No. COUNTIF tests cell values against criteria and does not inspect font formatting. Count the value or rule behind the color, filter by font color, or use VBA for directly applied font color.

Can I count red text created by conditional formatting?
Do not rely on a standard COUNTIF or the direct-font VBA function for the displayed color. Count the condition that triggers the conditional-formatting rule, or filter by color for a manual check.

Will the VBA count update when I change a font color?
Not necessarily. A formatting edit does not itself trigger a normal worksheet recalculation. Request a recalculation with F9, and remember that the function still checks direct font color rather than conditional-formatting appearance.

Can I count red text without enabling macros?
Yes. If the red formatting represents a value or rule, count that condition with a worksheet formula. For a one-time check, filter by font color and count visible nonblank records with SUBTOTAL.

Does the VBA function count red cell fills?
No. The example compares font color, which controls the text. A cell’s fill color is separate formatting, so this function does not count red backgrounds.

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.