SUMIF(S) Based on Cell Color

Add to Favorites
Author: | Edits: don

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 InsertModule on the menu. In the window that opens, paste the above macro.

70728ae1ea2e50176864221f73dcf381.png

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

ace757ad4fc07c3c27c96ff22a36d131.png

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

24a9458fe7b9319e190e92793e7d3ccb.png

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

81c7888dd4199aeed7dee746b3ea803d.png

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

7e8308d17e287f33c392c1daa7763c54.png

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)

b01f0f72c8ab92634351b131648caa6e.png

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*")

ee11d3ccd81ca9108f3787ed6e2a075a.png

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.

Question? Ask it in our Excel Forum


Excel Function: SUMIF(), SUMIFS()
Downloadable Files: Excel File

Similar Content on TeachExcel
Formulas Based on Cell Color - SUMIFS, IF, COUNTIF, Etc.
Tutorial: How to use a SUMIF or SUMIFS function (or any conditional function or formula) on cell bac...
SUMIF - Sum Values Based on Criteria in Excel
Tutorial: The SUMIF function allows you to sum values based on a single criteria. This function wor...
SUMIFS - Sum Values Based on Multiple Criteria in Excel
Tutorial: The SUMIFS function allows you to sum values that meet multiple criteria across multiple ...
Sum Values that Meet 1 of Multiple Conditions in Excel
Tutorial: How to sum values that equal one of many potential criteria; this is basically summing wi...
Sum Values that Equal 1 of Many Conditions across Multiple Columns in Excel
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...
Wildcards in Excel
Tutorial: Wildcards are characters that allow you to make more robust functions, searches, and filt...
Tutorial Details
Excel Function: SUMIF(), SUMIFS()
Downloadable Files: Excel File
Similar Content
Formulas Based on Cell Color - SUMIFS, IF, COUNTIF, Etc.
Tutorial: How to use a SUMIF or SUMIFS function (or any conditional function or formula) on cell bac...
SUMIF - Sum Values Based on Criteria in Excel
Tutorial: The SUMIF function allows you to sum values based on a single criteria. This function wor...
SUMIFS - Sum Values Based on Multiple Criteria in Excel
Tutorial: The SUMIFS function allows you to sum values that meet multiple criteria across multiple ...
Excel Forum