To add numbers in cells you have selected, select them and read Sum on Excel’s status bar; to total cells with a particular fill color, filter by that color and use =SUBTOTAL(109,B2:B100) on the values. These methods answer two different meanings of “highlighted”: selected cells and cells formatted with a color.
Selected cells: use the status bar
If “highlighted” means cells you have selected with the mouse or keyboard, the quickest answer is Excel’s status bar. Select the numeric cells, and look along the bottom of the Excel window for Sum. The displayed total is calculated from the selection; it does not enter a formula into the worksheet.
If Sum is not displayed, right-click the status bar and select Sum in its customization menu. You can also turn on Average, Count, Numerical Count, Minimum, and Maximum there. On a Mac, Control-click the status bar to open its customization options. The status bar is convenient for a quick check, but the result disappears when you change the selection and is not a saved worksheet calculation.
You can select a continuous range by clicking its first cell and dragging, or by selecting the first cell and then Shift-clicking the last. To include separate ranges, hold Ctrl on Windows while selecting each range; on a Mac, use Command. The status bar can summarize multiple selected areas, but take care not to select the same cells twice: overlapping selections can make the displayed result misleading or difficult to interpret.
Cells filled with a color: what Excel can and cannot do
A colored fill is formatting, not a value stored in the cell. Standard worksheet functions such as SUM and SUMIF evaluate values or conditions; they do not provide a normal built-in criterion meaning “this cell has a yellow fill.” That is why a formula such as SUMIF cannot directly total cells by manually applied fill color.
First decide what the color represents. If yellow means “approved,” “late,” or “over budget,” summing by that underlying status is usually more dependable than summing by appearance. If the color is only a visual marker and there is no corresponding data, filtering by fill color or using a VBA function may be appropriate. The best method also depends on whether the color is applied directly or produced by conditional formatting.
Sum a fill color with Filter by Color
For a one-time total, filter the rows to show the desired fill color, then use SUBTOTAL on the numeric column. This works well when the color is in the same column as the amounts or when each row’s color identifies whether its amount should be included.
- Make sure the data has a header row, then click inside the data range.
- Choose Data > Filter to add filter arrows to the headers.
- Open the filter arrow for the column containing the colored cells. Choose Filter by Color, then select the fill color to keep.
- Put the total formula in a cell outside the filtered list, or read it from the status bar.
For example, with numeric values in B2:B100 and a header in B1, enter =SUBTOTAL(109,B2:B100). If the visible values are 12, 18, and 25, the result is 55. Function number 109 tells SUBTOTAL to sum while excluding rows hidden by filtering and rows hidden manually. The ordinary number 9 also ignores filtered-out rows but includes manually hidden rows, so 109 is generally the clearer choice for a filtered total.
Filtering hides whole rows. If the color is in column A but the amounts are in column B, filter the color in A and subtotal B. If color is applied to individual cells in a column where rows contain unrelated records, filtering still acts on rows, not on separate cells within a row. Also note that a table or range needs usable headers for this workflow, and clearing the filter restores the other rows without deleting them.
Use a helper column when color represents a rule
A helper column is often the most reliable solution when color is a shorthand for a business rule. Instead of asking Excel to inspect formatting, store the status or qualifying condition in a column and total based on that value. The result is easier to audit, works without macros, and updates when the data changes.
For example, assume amounts are in B2:B10 and each row’s status is in C2:C10. If rows marked Yes should be included, enter =SUMIF(C2:C10,"Yes",B2:B10) in a separate cell. If the qualifying rows contain 20, 35, and 10, the total is 65. This works whether those rows are colored, uncolored, or displayed in a different color scheme.
For a more involved rule, add a helper column that labels each row as Include or Exclude, then sum based on that label. This makes the decision visible to coworkers and gives you a place to explain why a row qualifies. It also avoids a common maintenance problem: someone may change a fill color for presentation without intending to change the calculation.
Conditional formatting needs a different approach
If cells look highlighted because of conditional formatting, the displayed color may not be the cell’s direct fill color. Conditional formatting applies an appearance when its rule is true, so a formula or macro that checks ordinary cell fill may fail to match what you see. Filter-by-color behavior can also depend on the type of formatting and the Excel version; test it on a copy or a small sample before relying on it for a report.
The dependable approach is to total using the condition behind the formatting rule. For instance, if amounts in B2:B20 should be included whenever the corresponding scores in A2:A20 are at least 100, enter =SUMIF(A2:A20,">=100",B2:B20). If the qualifying amounts are 15, 20, and 30, the result is 65. This matches the rule rather than its visual effect. For a more complex rule, use helper columns or an appropriate multi-condition function based on the actual data logic.
When possible, inspect the conditional formatting rule through Home > Conditional Formatting > Manage Rules and identify the condition Excel is evaluating. Re-create that condition in a worksheet formula. This is more robust than trying to infer a calculation from a color that may change with a theme, formatting edit, or new rule.
Use VBA for totals based on direct fill color
If color itself is intentionally the criterion, a VBA user-defined function can compare each cell’s direct fill color with a sample cell. This is a specialized option, not a built-in Excel formula. In Excel for Microsoft 365 on Windows, press Alt+F11, choose Insert > Module in the Visual Basic Editor, and add the code below. On a Mac, open the Visual Basic Editor from the Developer tab or Tools > Macro; Excel for the web cannot run VBA macros.
Function SumByFill(SumRange As Range, ColorSample As Range) As Double
Dim Cell As Range
Dim Total As Double
' Add numeric cells whose direct fill matches the sample cell.
For Each Cell In SumRange.Cells
If Cell.Interior.Color = ColorSample.Interior.Color Then
If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
Total = Total + CDbl(Cell.Value)
End If
End If
Next Cell
SumByFill = Total
End Function
With amounts in B2:B10 and a sample cell D1 filled with the color to match, enter =SumByFill(B2:B10,D1) in a worksheet cell. If the matching numeric cells contain 12, 18, and 25, the result is 55. The sample cell’s fill can be anywhere in the workbook; the function compares its direct fill color with each cell in the range.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained, and follow your organization’s macro-security rules. Save a separate copy before running or modifying a macro: VBA changes cannot be undone with Ctrl+Z. This function reads direct fills through Interior.Color; it does not evaluate the visible result of conditional formatting. Excel may also not recalculate a formula just because you changed a cell’s formatting. After recoloring cells, force a recalculation with F9 on Windows or Fn+F9 on some Mac keyboards, or re-enter the formula if necessary.
Choose the method that fits the task
- Quick check of selected numbers: select the cells and use the status-bar Sum.
- One-off total of rows marked by a direct fill: use Filter by Color and SUBTOTAL.
- Repeatable calculation based on status or a rule: use SUMIF, a helper column, or another formula based on the underlying data.
- Color is the actual criterion and no data field represents it: consider the VBA function, accepting its macro and recalculation limitations.
- Color comes from conditional formatting: total using the rule’s condition rather than the displayed color.
For reports used by other people, formulas based on explicit data are usually the easiest to review and maintain. A color-only calculation can be hard to audit because two cells that look alike may have different formatting definitions, and a formatting change can alter which values are counted without changing the data.
Common problems and checks
- No Sum on the status bar: right-click the status bar and enable Sum. Also check that the selected cells contain numbers rather than numbers stored as text.
- The filtered total seems too large: confirm the filter is applied to the intended color column and that SUBTOTAL uses 109 if manually hidden rows should be excluded too.
- The color-based total misses cells: check for slightly different fills, theme colors, or conditional formatting. The VBA example matches direct color values, not merely colors that look similar.
- The total does not change after recoloring: formatting changes do not always trigger recalculation of a custom VBA function. Recalculate, or use an explicit status/helper column instead.
- The formula shows an error or the function name is unknown: VBA functions require macros to be available and enabled, and the workbook must retain the module. They do not run in Excel for the web.
Frequently Asked Questions
Can Excel sum cells by color without VBA?
For a one-time total, you can filter rows by fill color and use SUBTOTAL on the visible numeric range. Excel does not have a standard SUMIF condition for arbitrary fill color. For a reliable ongoing calculation, sum by the data or rule that the color represents.
How do I sum only the cells I select?
Select the cells and read Sum on the status bar. If it is missing, right-click the status bar and turn on Sum. This gives a quick display, not a formula saved in a worksheet cell.
Does SUMIF work with cell color?
No. SUMIF evaluates criteria against cell values, not ordinary fill formatting. Use a helper value such as a status label, filter by color with SUBTOTAL, or use a VBA function when direct fill is truly the criterion.
Can I sum cells highlighted by conditional formatting?
Use the condition that triggers the formatting rule in a formula, rather than relying on the displayed fill. For example, total amounts for scores over a threshold with SUMIF. Conditional formatting’s appearance is not the same as a directly applied fill.
Will the status-bar total appear in the worksheet?
No. The status bar shows a temporary summary of the current selection. To keep a total in a cell, enter an appropriate formula such as SUBTOTAL for filtered visible rows or SUMIF for a data-based condition.
Can Excel for the web run the color-summing VBA function?
No. Excel for the web does not run VBA macros. Use a status-bar summary, a filter and SUBTOTAL, or a formula based on a helper column or the underlying condition instead.
Related
- Excel Row Limit: How Many Rows Can a Worksheet Hold?
- Excel: Count How Many Times a Value Appears in a Range
- Excel Row Limit: How Many Rows Can a Sheet Hold?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel: Multiple Criteria, Examples and TipsYes. In Excel, SUMIFS adds numbers only when multiple conditions are met; each criteria pair can test a…
- UPPER Excel Formula: Capitalize Text in Excel CellsTo make every letter in text uppercase in Excel, use the UPPER function. If the original text is…
- Excel VLOOKUP Examples With a Drop-Down ListTo make a drop-down selection drive a lookup in Excel for Microsoft 365 on Windows, create a list…
- Excel Formula for Time Difference: Examples and FixesTo find the time between two values in Excel, subtract the start time from the end time: with…
- How to Get the Filter Option in Excel and Use It EasilyTo get the Filter option in Excel, click a cell in your data and select Data > Filter.…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.