Excel’s COUNTIF function cannot count cells by font or text color: its criteria test cell values, not formatting. To count colored text, filter the column by font color and count the visible records, or use a VBA function for a formula-based count of cells with directly applied font color.
Why COUNTIF cannot use text color
COUNTIF evaluates what a cell contains, such as text, a number, or a date. It does not inspect the cell’s font color, fill color, bold setting, or other formatting. There is no built-in criteria syntax such as “red font” that you can add to the function.
This distinction matters because two cells can display the same word in different colors, while two cells with different values can share a color. Formatting is a visual property; the value is the data the worksheet functions normally calculate with. Excel’s standard formulas do not provide a general way to query a cell’s displayed font color.
A common reason for wanting a color count is that color represents a status: red means overdue, green means approved, or blue means reviewed. If the color is assigned manually, it may be the only record of that status. If the color is generated by a rule, the underlying value or rule is usually a more dependable thing to count.
Count the underlying text when color represents a status
When a color corresponds to a word or code in the cells, count that value rather than its appearance. This approach is usually the easiest to maintain: it works in ordinary formulas, updates when data changes, and does not depend on macros or formatting settings.
For example, suppose cells C2:C100 contain status labels, and three of them say “Overdue.” With statuses in C2:C100, the formula is =COUNTIF(C2:C100,"Overdue"). It returns 3 if exactly three cells contain that text. The cells may be red, black, or unformatted; their font color does not affect the result.
If the status is stored in a separate column from the text whose color you see, use the status column as the COUNTIF range. For example, a table can store “Overdue” in a Status field and use conditional formatting to show the corresponding task name in red. Counting the status field is more robust than counting the appearance of the task name, especially if someone later changes the color theme or rule.
Check whether conditional formatting controls the color
Before choosing a counting method, find out whether the font color was applied manually or comes from conditional formatting. Select a colored cell and open Home > Conditional Formatting > Manage Rules. Check the rules that apply to the selected cells. If a rule sets the font color, the visible color is an outcome of a condition, not a simple stored text-color choice.
When possible, count the condition itself. For instance, if a rule colors cells whose status is “Overdue,” count the “Overdue” values with COUNTIF, as above. If a rule depends on a date, a number threshold, or another field, build the count from that same data condition. This avoids a mismatch in which a cell’s displayed color changes but a separate color-count method does not update.
Rules can overlap, and their order or “Stop If True” settings can affect which formatting appears. A color may therefore be a poor substitute for a clear status field. If the color is the only indication of a complicated rule, inspect the rule carefully before recreating its logic in a count.
Use Filter by Color for a quick count
For a one-time or occasional count, Excel’s filter menu can display records by font color without writing code. This is useful when a worksheet already uses manually applied colors and you want to inspect the matching rows. In Excel for Microsoft 365 on Windows:
- Select the data range or a cell in the table, then choose Data > Filter if filter arrows are not already shown.
- Open the filter arrow on the column containing the colored text.
- Choose Filter by Color, then select the font color to display. The menu offers colors present in that column.
- Count the visible records. The status bar may show a count when you select the visible cells; for a formula-based visible-row count, use a populated ID column as described below.
Filtering hides rows that do not match; it does not delete them or change their values. Clear the filter afterward to restore the full list. This method is less suitable when colors are used inconsistently, when you need a result that updates automatically in a report, or when you need to count blank colored cells. The status bar’s count can also depend on which cells you select, so check what is being counted.
Count visible matches with SUBTOTAL
A filtered count can be put in a cell with SUBTOTAL. Assume IDs are in A2:A7 and the font-colored values being filtered are in B2:B7. After filtering column B to one font color, enter =SUBTOTAL(103,A2:A7). If rows 2, 4, and 7 remain visible and each has an ID, the result is 3.
The function number 103 tells SUBTOTAL to count nonempty cells while ignoring rows excluded by a filter; it also ignores manually hidden rows. That is why the ID column is useful: it counts visible records even if the colored cell itself is blank. If you count the colored column instead, blank cells will not contribute to this count. The formula counts the rows currently visible under the filter, not the color directly, so changing or clearing the filter changes the result.
Use VBA for a formula that checks direct font color
If you need a worksheet formula to compare directly applied font colors, a small VBA user-defined function can do it. It is not a built-in Excel function. In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code below into the module. Save the workbook as a macro-enabled .xlsm file. On Mac, VBA is available in desktop Excel, but the keyboard shortcut and editor menus can differ; Excel for the web cannot run VBA macros.
Public Function CountFontColor(ByVal dataRange As Range, ByVal sampleCell As Range) As Long
Dim cell As Range
Dim targetColor As Long
Dim total As Long
Application.Volatile
targetColor = sampleCell.Font.Color
For Each cell In dataRange.Cells
If cell.Font.Color = targetColor Then
total = total + 1
End If
Next cell
CountFontColor = total
End Function
Assume the cells to compare are A2:A20 and D1 is a sample cell whose font has the color you want to count. Enter =CountFontColor(A2:A20,D1); if five cells in A2:A20 have the same directly applied font color as D1, the result is 5. This version counts every matching cell, including a blank cell if its font color matches. To count only populated records, use a range whose cells are all populated or modify the function to test for nonblank values.
This function reads the cell’s direct Font.Color formatting. It does not reliably count the color displayed by conditional formatting. Also, changing formatting alone may not trigger recalculation: press F9 to recalculate after a color edit if the result appears stale. Application.Volatile makes the function recalculate when Excel performs a calculation, but it does not make formatting changes into ordinary value changes.
Save a copy of the workbook before running or adding macros. Macro code cannot be undone with Ctrl+Z, and organizational security settings may prevent macros from running. Use only code you understand and trust. If you share the workbook with people who use Excel for the web or disable macros, the custom function will not be a dependable shared solution.
Limitations and common counting mistakes
- Font color is not fill color. A font-color method compares the color of the characters. It will not count cells based on their background fill.
- Direct formatting differs from rule-based formatting. A VBA function that checks
Font.Colorcan miss a color supplied by conditional formatting. - A color match does not validate the meaning. Someone may color a cell manually by mistake, or use a similar-looking shade. A count based on a status value is easier to audit.
- Formatting changes are not normal formula inputs. Standard worksheet formulas do not recalculate based solely on a font-color edit. This is a key reason color-driven counts can appear stale.
- Color accessibility matters. Do not make color the only way to identify a status. Include text labels, symbols, or another accessible indicator so the data remains understandable when color is hard to distinguish or unavailable.
There is also an older Excel 4 macro-function approach using GET.CELL in a defined name. It is a legacy technique rather than a normal worksheet function, requires extra setup, and has calculation and compatibility limitations. For most current workbooks, a meaningful status column, a filter, or a clearly documented VBA function is easier to explain and maintain.
Choosing the right method
Choose based on what the color means and how often the count must update:
- Use COUNTIF or another ordinary formula when the color represents a value, status, or rule that can be counted from the data. This is usually best for dashboards and shared workbooks.
- Use Filter by Color when you need a quick visual check and can accept a result tied to the current filter.
- Use SUBTOTAL with a populated record column when a filtered count must appear in a worksheet cell and update as you change the filter.
- Use VBA only when the actual manual font color is the information you must count and the workbook’s users can run macros.
If you control the workbook design, storing the category in a dedicated column and using conditional formatting to display it is usually the soundest long-term choice. The color then helps people scan the sheet, while formulas operate on explicit data rather than trying to interpret presentation.
Frequently Asked Questions
Can COUNTIF count cells with red font?
No. COUNTIF checks cell values and criteria, not font formatting. Use a value-based condition, filter by font color, or use a VBA function if you specifically need to compare directly applied font colors.
Can COUNTIF count cells with a colored background?
No. A cell’s fill color is formatting too, so COUNTIF cannot test it. Excel’s filter menu can filter by color, or VBA can be written to inspect fill formatting; choose a method that matches whether the color is manual or conditional.
Does the VBA method work with conditional formatting?
The function shown reads direct font color with Font.Color; it is not a reliable way to count conditional-formatting display colors. If a rule creates the color, count the rule’s underlying condition whenever possible.
Will the color count update when I change a cell’s font?
Not necessarily. Excel does not treat a formatting edit like a change to a formula input. With the VBA function, press F9 to recalculate if needed, and remember that the function still does not read conditional-formatting display color.
Can I use the VBA formula in Excel for the web?
No. Excel for the web does not run VBA macros or custom VBA worksheet functions. Use a value-based formula or an available filter instead, or open the macro-enabled workbook in desktop Excel.
What is the best method for a recurring report?
Store the status as text or another explicit value, then count that field with a normal formula. This is more reliable than relying on a manual font color, works better across Excel versions and platforms, and is easier for another person to verify.
Related
- Center Across Selection in Excel: Add It to Quick Access
- Change Block Capitals to Lowercase in Excel
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Percentage in Excel: Formulas for a ColumnTo calculate a percentage in an Excel column, divide each part by its whole, then format the results…
- How to Add a Sequence of Dates in Excel: 3 Easy WaysTo add a sequence of dates in Excel, enter a starting date and the next date in two…
- Sort in Excel and Move Entire Rows Without Splitting DataTo sort in Excel and move entire rows together, select the full data range, then choose Data >…
- How to Match Two Excel Sheets with VLOOKUP in ExcelTo match records between two Excel sheets, put the value you want to find in the first column…
- Excel Import CSV: Formula or the Right Import MethodExcel does not have a built-in worksheet formula that imports a CSV file from your computer. In Microsoft…
- How IFs Work in Excel: Formulas and ExamplesIn Excel, an IF formula tests a condition and returns one result when it is true and another…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.