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
- Change Block Capitals to Lowercase in Excel
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Count Dates Older Than Today: Formula and FixesTo count dates in Excel that are earlier than today, use =COUNTIFS(A2:A100,"
- Excel XLOOKUP Practice Examples: Formulas and TipsExcel XLOOKUP practice examples are easiest to learn by matching a lookup value to a column and returning…
- Concatenate Dates and Text in Excel: Formula GuideTo concatenate a date and text in Excel, convert the date to readable text with the TEXT function,…
- Excel Text Plus Cell Value: Formulas, Formatting, and FixesTo combine text with a cell value in Excel, join them with the ampersand operator (&). For example,…
- Excel GROUPBY With Multiple Criteria: Filter ArraysTo group Excel data while applying multiple row-level criteria, use the filter_array argument in GROUPBY and combine AND…
- Excel Day Value: Serial Dates and the DAY FunctionAn Excel day value can mean either the serial number Excel uses to store a date or the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.