SUMIF(S) Based on Cell Color

Add to Favorites
Author: | Edits: don

How to SUM cells with certain background colors.


Prepare the Data

SUMIF Cells with Certain Colors

SUMIFS Cells with Certain Colors


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.


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



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".




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.

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

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...