SUMIF(S) Based on Cell Color

How to SUM cells with certain background colors.

Sections:

Prepare the Data

SUMIF Cells with Certain Colors

SUMIFS Cells with Certain Colors

Notes

Prepare the Data

To get the data ready, we need to put the cell colors into an adjacent column so that we can use the SUM functions on them. To do this, we use a macro that will output the numerical representation of each color into Excel.

Here is the macro:

Sub ListColors()

For Each c In Selection

    c.Offset(0, 1).Value = c.Interior.Color

Next c

End Sub

Install the macro like this: Hit Alt + F11 to go to the VBA Window and go to Insert > Module on the menu. In the window that opens, paste the above macro.

Next, go to the data table and insert a new column directly to the right of the old column.

Next, select all of the cells that have background colors and run the macro.

Hit Alt + F8 to get to the macro window, select the macro, and click the Run button.

Now, you should see the new column filled with numbers that are unique to each color.

Your data is now setup for the SUM functions.

SUMIF Cells with Certain Colors

(make sure to setup your data like in the previous section)

Here is a sample SUMIF that you can now use to sum only the light-grey cells.

=SUMIF(C2:C9,C6,E2:E9)

You could also hard-code the number in like this:

=SUMIF(C2:C9,13750737,E2:E9)

SUMIFS Cells with Certain Colors

(make sure to setup your data like in the first section of this tutorial)

Here is a SUMIFS function (available in Excel 2007 and later) that will sum all cells that are not light-green and do not begin with "gsc".

=SUMIFS(E2:E9,C2:C9,"<>8122747",A2:A9,"<>gsc*")

Notes

The most important thing is to prepare the data correctly. The numbers generated by the macro are what allow you to include or exclude values based on a cell's color.

Make sure that the column where you put the numbers for the colors is directly to the right of the column with the colors; the macro will automatically input data in the first column to the right of the cells that you select.

You can hide the Color Value column once you finish making your formulas; it will still work and then it won't mess-up the design of your table.

Download the attached project file so you can see these examples in Excel.