Can Excel LET or LAMBDA Sum Cells by Fill Color?
Question details
Determine whether Excel LET or LAMBDA functions can replace a custom VBA function that totals values according to a cell's fill color.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to add numeric cells that share a particular interior fill color.
- Observed behavior
- LET and LAMBDA evaluate values and formula results but do not expose worksheet formatting properties such as Interior.Color or ColorIndex.
Check whether the fill color is manually applied or created by conditional formatting. If conditional formatting creates the color, identify the rule or condition first, because that condition can often be used directly in a formula.
Use the conditional-formatting rule in a formula instead of reading color
Use this method when the cell color is the result of a known conditional-formatting rule. It is more reliable than totaling by appearance and recalculates automatically.
Worksheet functions, including LET and LAMBDA, can work with cell values and logical tests, but they cannot inspect a cell's Interior.Color or ColorIndex. Replace the color test with the condition that causes the color whenever possible.
Select a colored cell, then go to Home > Conditional Formatting > Manage Rules. Note the formula, value threshold, or other condition that applies the fill color.
In an empty cell, use SUMIF, SUMIFS, or SUMPRODUCT with the same condition. For example, if values in B2:B100 are green when A2:A100 equals "Approved", enter =SUMIF(A2:A100,"Approved",B2:B100).
If the formula is long, use LET to name ranges and criteria, or save a reusable value-based calculation through Formulas > Name Manager > New. Do not expect LET or LAMBDA to return a fill color.
Use a VBA function when manually applied fill color must be inspected
Use this method only when the colors are manually set or when no equivalent value-based condition exists.
Build Color-Independent Totals in WPS Spreadsheets
WPS Spreadsheets can total values from the same criteria that drives conditional formatting, so your results are based on data rather than a display color. Use conditional formatting for visual status and SUMIF or SUMIFS for a recalculating total.
- 1. Identify the data condition: In WPS Spreadsheets, select the formatted range and open Home > Conditional Formatting to review the value, text, or formula condition that controls the color.
- 2. Add a criteria column if needed: Enter a status or category such as Approved, Pending, or Overdue in a nearby column so the condition is stored as worksheet data.
- 3. Calculate the matching total: Select the result cell and enter a formula such as =SUMIF(A2:A100,"Approved",B2:B100), replacing the ranges and criterion with your own.
- 4. Keep the visual rule aligned: Use Home > Conditional Formatting to apply the same Approved condition to the value range, ensuring the displayed color and calculated total represent the same records.
Frequently Asked Questions
Can LET read a cell's background color in Excel?
No. LET only assigns names to values or calculation results within a formula. It does not provide access to formatting properties such as Interior.Color, ColorIndex, font color, or borders.
Can a LAMBDA function total cells with the same fill color?
Not by inspecting the fill color itself. LAMBDA can package a value-based formula, such as a SUMIFS calculation using a status column, but it cannot read a worksheet cell's fill formatting.
Why does a color-based VBA total not update after I change a fill color?
A fill-color change may not trigger worksheet recalculation because the cell value did not change. Press F9, choose Formulas > Calculate Now, or add VBA logic that recalculates the function after formatting changes.
What is the best alternative to summing by conditional formatting color?
Use the same condition that drives the formatting in SUMIF, SUMIFS, or SUMPRODUCT. For example, total rows whose status is "Overdue" rather than attempting to total the cells currently displayed in red.
