# SUMIF(S) Based on Cell Color

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. 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*")`` ## Question? Ask it in our Excel Forum

Excel Function: SUMIF(), SUMIFS()

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 ba...
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()