Forum Discussion
SUMIF cell has no background color without Macro or VB
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the code listed below into the module.
Switch back to Excel.
Let's say the amounts are in B2:B20.
In another cell, enter the formula
=SumUncolored(B2:B20)Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Remark: simply coloring a cell, or removing a cell's fill color, won't make Excel update the result of the formula. It will happen automatically whenever Excel next recalculates the sheet. You can force Excel to do so by pressing F9.
Sample workbook attached.
Function SumUncolored(rng As Range) As Double Dim cel As Range Application.Volatile For Each cel In rng If cel.Interior.ColorIndex = xlColorIndexNone Then SumUncolored = SumUncolored + cel.Value End If Next cel End Function
Summing by color requires VBA (or an add-in).
As an alternative, use a column in which you enter x (or 1) if the amount in that row has been paid.
You can then use an ordinary SUMIF formula:
=SUMIF(paid_range, "x", amount_range)
- Hi Hans, can you help with the VBA code?
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the code listed below into the module.
Switch back to Excel.
Let's say the amounts are in B2:B20.
In another cell, enter the formula
=SumUncolored(B2:B20)Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Remark: simply coloring a cell, or removing a cell's fill color, won't make Excel update the result of the formula. It will happen automatically whenever Excel next recalculates the sheet. You can force Excel to do so by pressing F9.
Sample workbook attached.
Function SumUncolored(rng As Range) As Double Dim cel As Range Application.Volatile For Each cel In rng If cel.Interior.ColorIndex = xlColorIndexNone Then SumUncolored = SumUncolored + cel.Value End If Next cel End Function- Awesome, at least the VBA is not a mile long, that is why I dreaded using a VBA.
