Forum Discussion

Hugh_Wilbarger's avatar
Hugh_Wilbarger
Copper Contributor
Oct 17, 2023
Solved

Create a conditional sum of cell contents based on cell color

How to create a numerical sum of cells if cell color fill = 'No Fill" or if cell color fill is a certain color such as yellow?

  • Hugh_Wilbarger 

    In Excel, you can create a conditional sum of cell contents based on cell colors using a combination of Visual Basic for Applications (VBA) and a User-Defined Function (UDF). Here's how you can do it:

    Step 1: Enable Developer Tab If you haven't already enabled the Developer tab in Excel, do the following:

    1. Go to the "File" tab.
    2. Click on "Options."
    3. In the Excel Options dialog, select "Customize Ribbon."
    4. Check the "Developer" option in the right pane.
    5. Click "OK."

    Step 2: Create a User-Defined Function (UDF) in VBA

    1. Press ALT + F11 to open the VBA editor.
    2. Click "Insert" in the menu and select "Module" to insert a new module.
    3. Copy and paste the following VBA code into the module window:

    vba code:

    Function SumByColor(rng As Range, cellColor As Range) As Double
        Dim cell As Range
        Dim total As Double
        Application.Volatile
    
        For Each cell In rng
            If cell.Interior.Color = cellColor.Interior.Color Then
                total = total + cell.Value
            End If
        Next cell
    
        SumByColor = total
    End Function
    1. Close the VBA editor.

    Step 3: Use the UDF in Excel

    Now, you can use the UDF in your Excel worksheet to sum cells based on their color. Assuming you have a range of numbers in cells A1:A10 and you want to sum the cells with a yellow background:

    1. In a different cell, type the following formula:

    =SumByColor(A1:A10, B1)

      • A1:A10 is the range you want to sum.
      • B1 refers to the cell containing the color you want to match (B1 should have the same color as the cells you want to sum).
    1. Press Enter. The formula will sum the values in cells with the same background color as the color in cell B1.

    This formula will sum the values in the specified range based on the cell color, which can be either "No Fill" or any specific color you choose. You can customize it to match other colors by changing the cellColor reference to another cell with the desired color or by specifying the color as an RGB value. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Hugh_Wilbarger 

    In Excel, you can create a conditional sum of cell contents based on cell colors using a combination of Visual Basic for Applications (VBA) and a User-Defined Function (UDF). Here's how you can do it:

    Step 1: Enable Developer Tab If you haven't already enabled the Developer tab in Excel, do the following:

    1. Go to the "File" tab.
    2. Click on "Options."
    3. In the Excel Options dialog, select "Customize Ribbon."
    4. Check the "Developer" option in the right pane.
    5. Click "OK."

    Step 2: Create a User-Defined Function (UDF) in VBA

    1. Press ALT + F11 to open the VBA editor.
    2. Click "Insert" in the menu and select "Module" to insert a new module.
    3. Copy and paste the following VBA code into the module window:

    vba code:

    Function SumByColor(rng As Range, cellColor As Range) As Double
        Dim cell As Range
        Dim total As Double
        Application.Volatile
    
        For Each cell In rng
            If cell.Interior.Color = cellColor.Interior.Color Then
                total = total + cell.Value
            End If
        Next cell
    
        SumByColor = total
    End Function
    1. Close the VBA editor.

    Step 3: Use the UDF in Excel

    Now, you can use the UDF in your Excel worksheet to sum cells based on their color. Assuming you have a range of numbers in cells A1:A10 and you want to sum the cells with a yellow background:

    1. In a different cell, type the following formula:

    =SumByColor(A1:A10, B1)

      • A1:A10 is the range you want to sum.
      • B1 refers to the cell containing the color you want to match (B1 should have the same color as the cells you want to sum).
    1. Press Enter. The formula will sum the values in cells with the same background color as the color in cell B1.

    This formula will sum the values in the specified range based on the cell color, which can be either "No Fill" or any specific color you choose. You can customize it to match other colors by changing the cellColor reference to another cell with the desired color or by specifying the color as an RGB value. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

    • NicoleT2385's avatar
      NicoleT2385
      Copper Contributor
      This is great! I keep getting a circular reference when I try to sum the cells in one row but it works great when I sum a column. Any suggestions for summing them across i.e. C1:K1 instead of A1:A10
    • espieth's avatar
      espieth
      Copper Contributor

      NikolinoDE 

      This is awesome! Is there a way you can edit it to report as a blank cell if the total is 0?

Resources