Forum Discussion

Janedb's avatar
Janedb
Iron Contributor
Jul 05, 2023
Solved

SUMIF cell has no background color without Macro or VB

Good day, can someone assist with a formula to use to sum if the cell color is blank?

I mark all cells green if paid and need to calculate the remaining cells that have not been marked green.

excel
  • HansVogelaar's avatar
    HansVogelaar
    Jul 05, 2023

    Janedb 

    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

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 05, 2023

    Janedb 

    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)

    • Janedb's avatar
      Janedb
      Iron Contributor
      Jul 05, 2023
      Hi Hans, can you help with the VBA code?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 05, 2023

        Janedb 

        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

Resources