Forum Discussion
Create a conditional sum of cell contents based on cell color
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.
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.
Excellent! Worked perfectly.
- 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
This is awesome! Is there a way you can edit it to report as a blank cell if the total is 0?
Hello,
I followed the steps below and it didn't work. I'm extremely new to the VBA and macros, so what did I do wrong.
Step 1 - Open the VBA editor
Step 2 - On my personal.xlsb - inserted a new module
Step 3 - Copy and pasted the code from your instructions below
Step 4 - Named the module SumbyColor
Step 5 - Clicked save
Below is the result in my VBA editor
I closed the VBA editor and type =SumbyColor(c6:c15,c7) and the result was #name?
Help 😕 , please.
- You should not give the module the same name as the function - that confuses Excel. Rename the module.
- Since you created the function in PERSONAL.XLSB, use it like this:
=PERSONAL.XLSB!SumByColor(C6:C15, C7)
I used your sum by color formula and it worked great. However, after I shared the file using Microsoft 365, the formula stopped functioning and instead shows: #NAME?
I figure it has to do with including the module with the vba code? How do I include it with the shared file?
As macros don't work in Excel Online, if you're sharing the file via OneDrive or SharePoint, ask them to open it with the desktop version by clicking the "Open in Desktop App" button from the online viewer.
NikolinoDE It doesn't seem to recognize cell colors that are created via conditional formatting
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 is untested backup your file.
Function SumByColor(rng As Range, cellColor As Range) As Double Dim cell As Range Dim total As Double Dim color As Long Application.Volatile ' Get the color of the reference cell color = cellColor.DisplayFormat.Interior.Color For Each cell In rng ' Check if the cell's display format color matches the reference color If cell.DisplayFormat.Interior.Color = color Then total = total + cell.Value End If Next cell SumByColor = total End FunctionStep 3: Use the UDF in Excel
Now, you can use the UDF in your Excel worksheet to sum cells based on their display color (including conditional formatting):
- 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)
Works great now with conditional formatting. Thanks!
- Worked perfectly. Thank you so much NikolinoDE!!
