Forum Discussion
Hugh_Wilbarger
Oct 17, 2023Copper Contributor
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?
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:
- Go to the "File" tab.
- Click on "Options."
- In the Excel Options dialog, select "Customize Ribbon."
- Check the "Developer" option in the right pane.
- Click "OK."
Step 2: Create a User-Defined Function (UDF) in VBA
- Press ALT + F11 to open the VBA editor.
- Click "Insert" in the menu and select "Module" to insert a new module.
- 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
- 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:
- 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).
- 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.
- NikolinoDEGold Contributor
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:
- Go to the "File" tab.
- Click on "Options."
- In the Excel Options dialog, select "Customize Ribbon."
- Check the "Developer" option in the right pane.
- Click "OK."
Step 2: Create a User-Defined Function (UDF) in VBA
- Press ALT + F11 to open the VBA editor.
- Click "Insert" in the menu and select "Module" to insert a new module.
- 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
- 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:
- 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).
- 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.
- mmonosonCopper Contributor
Excellent! Worked perfectly.
- NicoleT2385Copper ContributorThis 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
- espiethCopper Contributor
This is awesome! Is there a way you can edit it to report as a blank cell if the total is 0?