Formulas Based on Cell Color - SUMIFS, IF, COUNTIF, Etc.

How to use a SUMIF or SUMIFS function (or any conditional function or formula) on cell background color.

There is no default way to do this in Excel, which means that we have to go through a rather annoying multi-step process. That said, there are many ways that you can achieve this result; however I will show you the method that allows you the most versatility and will work when building large SUMIFS functions or any other kind of conditional function such as COUNTIFS or IF.

Section:

Use Conditional Functions on Cell Background Color

Notes

Use Conditional Functions on Cell Background Color

This does require a macro, but it's not difficult to use and I'll walk you through every step.

1. Add a new column to your data set. This column will store the values that we use to perform the SUMIF or COUNTIF or IF. Make sure the new column is directly to the right of the column with the background colors.
   
2. Now, we add a small macro.
   Hit Alt + F11 to go to the VBA Window and then go to Insert > Module.
   
   
3. In the window that opens, paste this macro:
   

   Sub ListColors()
   
   For Each c In Selection 
   
       c.Offset(0, 1).Value = c.Interior.Color 
   
   Next c 
   
   End Sub

   
   
   Once you've done that, hit Alt + F11 to go back to the spreadsheet.
4. Select the cells with the background color.
   
5. Hit Alt + F8 and select the macro that we just added and click the Run button.
   
6. Now, you will have a column of numbers that represents each color.
   
7. Once you have these values for the numbers, you can then use them in any conditional function or formula.
   You simply refer to the Color Value column when making conditions that limit by color.

Example Formula

Let's SUM all values that start with "gsc" but that are not light green.

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

Important: this macro will put the Color Value numbers in the column that is directly to the right of the column that you selected that has the background colors - if you don't make a new column for it, it will overwrite whatever data is currently there.

Notes

There are many ways to do this kind of thing in Excel, and you can even make custom functions that SUM or COUNT based on color, but I find it more useful to have a separate column for Color Values that you can then use any way you like in any kind of formula or function.

If you don't like the Color Value column or it messes-up the look of your data table, simply hide it after you finished making conditions based on it.

Download the sample file for this tutorial so you can work along.