How To SUM Cells in Excel Based on Background Color [2023]

No doubt Excel has some really cool functions. You can even enter a formula without using one. But the thing is, there aren’t any direct ways to sum cells by color. However, you can create your own macro to automate this task.

Don’t want to add any macros to this workbook?

Don’t worry, we will show you how to use SUMIF function to sum cells based on background color. So, Stay along.

How To Assign Colors To Cells in Excel

It’s important for beginners to know the exact way of filling a cell with colors. You can either use the Fill Color Option or you can use Conditional Formatting. Both works just fine.

For those who don’t know how to add colors to cells, follow the steps below:

1. Open the Excel spreadsheet and select the cell you want to color.
2. Click on the Home Tab in the ribbon at the top of the Excel Window.
3. Locate the Font Section and click on the Fill Color option.
4. Choose from the variety of colors from the drop down menu and thus the selected cells will be filled with the color you chose.

You can either use the alternate version of this method i.e, using the Conditional Formatting. We use conditional formatting to automatically apply colors to cells based on certain criteria.

To do this, select the cells that you want to format. Click on the Conditional Formatting button in the Styles section of the Home tab. Choose the formatting rule that you want to apply. This will open a dialog box where you can set the formatting options, including the colors that you want to use.

Use SUMIF To Sum Cells Based on Background Color

As told earlier, Excel doesn’t have any direct way of adding numbers depending on colored cells. You have to use certain Excel functions to automate this task, including a VBA code of your own (more on that later).

So, now let’s take a look at the image down below.

We don’t want the total of all the highlighted cells. Instead, we want to sum only if the color is red in Excel. For that, we need the SUMIF Function.

The SUMIF function is the combination of both SUM and IF statements, just like using SUMIFs for multiple columns. But here’s the catch.

You have to manually name the texts of the used color. For Example, if you highlighted a cell in orange, you have to add another column and type in Orange. Only after that you will be able to use the SUMIF formula appropriately.

Let’s break down the sequence for you.

1. Open your data set and fill the cells with necessary colors.
2. Add another column beside the highlighted ones and name it Cell Colors.
3. Insert the formula =SUMIF in a separate blank cell.
4. Type =SUMIF(B2:B13,”Red”,A2:A13) for adding values of cells shaded in Red.
5. Press Enter for Excel to return a result.

Similarly, for finding and cells marked in Yellow and Green, type

and

As you are seeing, it’s difficult to add another column and type all the color names, although this method works perfectly if you want to sum colored cells in excel without VBA.

But, what if we write every color code automatically and based on that code, you just enter the reference cell?

For that, we need a custom macro. Let’s move on to the next section to learn more.

Sum By Color in Excel Using VBA

To add a macro, go to Developer Tab and select the Visual Basic option. Or you can use the shortcut key Alt+F11. Insert a new module and add your VBA code.

The user defined function (UDF) we will be using is:

Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function

Notice that there aren’t any color names inserted here. Instead, there are some color index numbers. We will be using these numbers to add our values.

After you have inserted and ran the VBA code, select any cell and type in =ColorIndex.

So, the syntax we will be using is:

Let’s say, I want to add up all the values marked in green. The index code for green is 50. So, my formula will be:

Press Enter and you will get your desired results.

Now, let’s elongate the code a little and use the SumByColor Function.

1. Open your Excel Worksheet and go to the Developer Tab.
2. Click on Visual Basic and type in the following code:

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
 If cl.Interior.ColorIndex = ColIndex Then
 cSum = WorksheetFunction.Sum(cl, cSum)
 End If
Next cl
SumByColor = cSum
End Function

Return to the Excel Worksheet and type the =SumByColor argument. Type =SumByColor(A2,A2:A13) if you wish to add the value from the color of A2 and press Enter.

Here, A2:A13 denotes the range of cells from which you want the addition to occur. And A2 denotes the reference cell.

Similarly, if you want the-

Sum of Yellow Cells:

As ‘A2’ is the address of the yellow cell and A2: A13 is the range to be added.

Sum of Green Cells:

As ‘A3’ is the address of the green cell and A2: A13 is the range to be added.

Sum of Red Cells:

As ‘A4’ is the address of the red cell and A2: A13 is the range to be added.

SumByColor Not Working in Excel?

Sum by color won’t work in Excel if you protect your worksheet. Or if you added a conditional formatting alongside the macro code. Unprotect the worksheet and remove the formatting to fix this problem.

You can un-protect the worksheet by going to the review tab and clicking on the unprotect sheet button.

FAQ

Q: Can you sum by color in Excel?

A: There aren’t any straightforward ways to sum cells by color. However, you can use some built-in functions like the SUMIF, SUBTOTAL, and the GET.CELL formula. Additionally, creating a VBA code will be an icing on the cake.

Q: Can you use SUMIF for colors?

A: SUMIF Formula is the combination of the SUM and IF functions. Hence, it’s a handy approach to use SUMIF for adding cells based on colors.

Q: Can you use COUNTIF to check for background color?

A: COUNTIF function is used to test values. For Example: If you have a dataset containing colorful cells, the COUNTIF function only finds out the number of repeated colored cells. If I have 30 purple colored cells, the COUNTIF function will return a value of 30.

Final Words

Summing up cells with colors is easy if you add a macro. However, if you don’t want to add vba codes, you can use the SUMIF functions too.

We prefer using the macro code because you have to manually add color texts while using SUMIF function.