Excel’s SUMIF function cannot directly test a cell’s text (font) color. To total values by color, use a filter and SUBTOTAL, record the category in a helper column and use SUMIF, or run a VBA macro when the color itself is the information you need to check.
Why SUMIF cannot use text color as a criterion
SUMIF evaluates cell contents, such as numbers, text, dates, and comparison criteria. Font color is formatting, not a value stored in the cell, so a criterion such as “blue font” is not something the function can test. Changing a cell’s font color therefore does not make an ordinary SUMIF return a different total.
This distinction matters because colors can be applied in different ways. Someone might select text and choose a font color manually, or a Conditional Formatting rule might display the text in a color when a condition is true. In either case, the visible color is presentation. If the color represents a business fact—such as a status, priority, or approval—storing that fact as text in a separate column is generally more dependable than making formatting carry the meaning.
Choose a method based on what the color means
Before choosing a workaround, ask why the cells are colored. If red means “overdue,” the most reliable solution is usually to sum rows whose due date or status makes them overdue. If someone has manually colored selected cells and there is no underlying rule or category, filtering or a macro may be more appropriate.
- The color represents a real category: Put the category in a column, then sum by that text with
SUMIF. - You need a quick total for visible rows: Filter by font color and use
SUBTOTAL. - You must inspect the displayed font color itself: Use a VBA macro, with the limitations described below.
For a report that will be reused or reviewed by other people, category data is usually easier to maintain and audit than a color-dependent calculation.
Use a helper column for a stable SUMIF
Add a column such as “Color category” or, better, the actual meaning of the color, such as “Approved” or “Review.” If the source data can be updated, make that column part of the normal data-entry process. Then the result depends on cell values rather than formatting, so it is easier to check, sort, filter, and use in other formulas.
For example, suppose A2:A6 contains item names, B2:B6 contains amounts 12, 8, 15, 20, and 5, and C2:C6 contains the recorded categories Blue, Red, Blue, Red, and Blue. With those ranges, the formula is =SUMIF(C2:C6,"Blue",B2:B6). It returns 32, from 12 + 15 + 5. The font color of the cells is irrelevant to this result; the text in column C supplies the criterion.
If color is applied because of a rule, use that rule’s underlying condition where possible. For instance, if amounts qualify because a score is at least 100, summing by the score is more robust than trying to total the colored amounts. With scores 120, 90, 150, and 80 in A2:A5, and corresponding amounts 10, 20, 30, and 40 in B2:B5, =SUMIF(A2:A5,">=100",B2:B5) returns 40, the sum of 10 and 30. This approach also keeps the total correct if the formatting rule or display theme changes.
Filter by font color and total the visible rows
For a one-time check, Excel can filter a column by font color. In Microsoft 365 for Windows, select a cell in the data, choose Data > Filter, open the filter arrow for the colored column, and choose Filter by Color > Font Color and the color you want. The exact options shown depend on the colors present in that column. This hides rows that do not match; it does not change their values.
To total only the visible amount cells, use SUBTOTAL. If the amounts are in B2:B100, the formula is =SUBTOTAL(109,B2:B100). The result is the sum of the visible numeric amounts after filtering. Function number 109 sums while ignoring rows hidden by a filter and rows hidden manually. If you need to include manually hidden rows while still excluding filtered-out rows, use function number 9 instead: =SUBTOTAL(9,B2:B100).
This is a practical choice when you want to inspect or report on a color group without writing code. Its limitation is that the total changes with the filter state: clear the filter, and it totals the newly visible rows. It is not a permanent formula that independently identifies a color group in every context. In Excel for Mac, the filter-by-color option is available in supported desktop versions, though ribbon placement and wording can vary slightly by release.
Use VBA when the actual font color must be checked
A VBA macro can loop through rows, compare the displayed font color in a category column with a sample cell, and add matching amounts. The example below assumes labels are in A2 downward, amounts are in column B, and D1 is a sample cell formatted with the color to match. It uses DisplayFormat.Font.Color, so it checks the displayed font color, including color produced by Conditional Formatting. It reports the sum in a message box rather than creating a worksheet formula.
On Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) before running it. On a Mac, use Tools > Macro > Visual Basic Editor if that menu is available, or the Visual Basic Editor command on the Developer tab; keyboard shortcuts can differ by keyboard and Excel release.
Sub SumByDisplayedFontColor()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
Dim targetColor As Long
Dim total As Double
Set ws = ActiveSheet
targetColor = ws.Range("D1").DisplayFormat.Font.Color
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Add amounts in column B when column A matches D1's displayed font color.
For rowNum = 2 To lastRow
If ws.Cells(rowNum, "A").DisplayFormat.Font.Color = targetColor Then
If IsNumeric(ws.Cells(rowNum, "B").Value) Then
total = total + CDbl(ws.Cells(rowNum, "B").Value)
End If
End If
Next rowNum
MsgBox "Total for displayed font color: " & total, vbInformation
End Sub
For the earlier example, put the target font color in D1 and ensure the matching labels are in column A. If the matching rows have amounts 12 and 15, the macro reports 27. The code assumes the active sheet is the one containing the data; activate the correct sheet before running it. It also treats any matching font color as a match, so a blank label with the same displayed color could be included, though a blank amount contributes nothing.
Macros need permission to run, and a macro-based total is not a live worksheet formula. Run the macro again after changing data or formatting. Save a copy of the workbook before running VBA: macro actions cannot be undone with Ctrl+Z. Avoid enabling macros in files you do not trust.
Conditional Formatting and color-related pitfalls
Conditional Formatting often explains why a color-based total seems inconsistent. The displayed color can change when a value changes, a rule is edited, or the order of rules affects which format applies. A plain VBA check of Font.Color may see the cell’s underlying font setting rather than its displayed conditional format. The sample macro uses DisplayFormat.Font.Color to account for that in a macro procedure.
Do not assume a color is a unique identifier. Two colors that look alike can have different underlying color values, especially if a workbook uses theme colors, shades, or tints. Conversely, a theme change may alter how a color looks. If a macro unexpectedly misses rows, check that the sample cell and target cells really have the same displayed formatting and that the intended column—not the amount column—is being compared.
Another common problem is treating font color and fill color as interchangeable. The macro above checks font color, not the cell background. A fill-color task requires comparing the appropriate fill property instead, and conditional formatting can affect displayed fills too. Make the distinction explicit before building a workaround.
When not to use color as data
Color is useful for quickly scanning a sheet, but it is a fragile sole source for a calculation. It can be missed by someone entering data, removed by pasting formats, or changed for accessibility or printing without intending to alter the underlying meaning. A category column keeps the logic visible and lets you apply color separately with Conditional Formatting.
If the purpose is to color rows based on a category, record that category first and then use Home > Conditional Formatting to display it consistently. The exact rule depends on the workbook layout. That design separates the data (what the row means) from the presentation (how it looks), and makes ordinary formulas, filters, PivotTables, and later analysis more reliable.
Frequently Asked Questions
Can SUMIF use a font color as its criteria?
No. SUMIF checks cell values, not font formatting. Use a helper column, filter by color with SUBTOTAL, or run VBA if the font color itself must be inspected.
Can I sum cells by text color without VBA?
Yes, for a quick total: filter the relevant column by font color and use SUBTOTAL on the amount range. For a reusable, independent total, store the category as data and use SUMIF.
Does SUMIF work with Conditional Formatting colors?
No. A color produced by a Conditional Formatting rule is still formatting, not a criterion that SUMIF reads. Sum using the value or condition that triggers the rule instead, or use a VBA macro that checks displayed formatting.
Why does my color total not update when I change formatting?
Formatting changes do not normally trigger recalculation of a formula based on cell values. A macro must be run again after relevant changes; a filter-based SUBTOTAL reflects the rows currently visible after the filter is applied.
Can I sum by cell fill color instead of font color?
The same general limitation applies: SUMIF does not test fill formatting either. Filter by fill color for a quick visible-row total, or use a carefully designed macro or, preferably, a data column that records the category.
Related
- Excel Shopping List Template: Create, Organize, and Print
- Excel: Shade the Area Under a Curve
- Excel Savings Plan Template: Build a Monthly Goal Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel: How Many Days Between Dates? Simple FormulasTo find how many days separate two dates in Excel, subtract the start date from the end date:…
- Excel Unique Dropdown List: Create One That UpdatesTo create a unique dropdown list in Excel for Microsoft 365, generate a deduplicated list with UNIQUE, then…
- Excel Formula Line Break: How to Add One in a CellTo put a line break in an Excel formula, join the text with CHAR(10), then turn on Wrap…
- Excel Compare Columns in Two Sheets: Methods and FormulasTo compare values in the same row, put this formula in a spare column and fill it down:…
- Excel Show Numbers in K: Custom Formats and FormulasTo show Excel numbers in thousands with a K suffix while keeping the original values intact, select the…
- Excel Time Addition: Formulas, Formatting, and FixesTo add times in Excel, enter the values in cells and add them with a formula: if a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.