How to Use Excel Formulas to Get Values Based on Cell Color?

Have you ever wanted to extract values from colored cells in Microsoft Excel? Maybe you have a spreadsheet where certain cells are highlighted in red, green, yellow, or another color to indicate something about their values. Fortunately, it’s possible to write Excel formulas that return values based on cell colors.

In this article, we’ll cover step-by-step how to set up formulas in Excel that allow you to pull out values from colored cells. You’ll learn multiple methods to achieve this, including using Excel’s conditional formatting feature along with formulas like SUMIF, COUNTIF, AVERAGEIF and more. By the end, you’ll have several options for retrieving values based on the interior shading color of cells in any Excel worksheet.

Why Get Values from Colored Cells in Excel?

There are many scenarios where you may want to extract cell values by color in Excel:

• Analyzing data where colors represent categories, priorities, or statuses
• Creating reports that show totals/averages of colored subsets of data
• Identifying key datapoints that have been manually highlighted
• Checking which colored cells contain certain numeric thresholds
• Summarizing results from worksheets that use color-coding

Whatever your reasons for needing to get values from colored cells, Excel’s built-in tools and formulas provide multiple ways to accomplish this. The best method will depend on your specific spreadsheet setup and goals.

Method 1: Filter by Color and Use SUBTOTAL

One straightforward way to sum values in colored cells is to:

1. Select your data range
2. Go to Data > Filter to enable filters
3. Click the filter arrow and select Filter by Color
4. Choose the color of the cells you want to sum
5. With the colored rows filtered, enter a SUBTOTAL formula like:

=SUBTOTAL(9,B2:B100)

The “9” in this formula includes only visible cells in the sum. The filtered color will be the only visible rows included.

To get the average of colored cells instead of the sum, use “101” instead of “9” in the SUBTOTAL formula. For a count of colored cells, use “2” or “102”.

This method works well for quickly summing, averaging or counting the values in a specific cell color that you select using the Filter feature. However, if you want more dynamic formulas that automatically adjust based on color without manual filtering, try the next techniques.

Pros and Cons of Filtering and SUBTOTAL

Filtering by color and using SUBTOTAL is a good choice when you need a one-time calculation of values from a specific colored subset of your data. It allows you to visually select the exact color you want and instantly see the matching cells.

However, the downside is that it’s a manual process that must be redone any time your data or colors change. The SUBTOTAL result won’t automatically update if you modify cell values or recolor cells.

Method 2: Use GET.CELL and SUM/AVERAGE/COUNT

A more advanced way to pull values from certain colored cells is with the GET.CELL function. This allows you to retrieve the color of a cell, which you can use inside an IF statement to conditionally sum values.

Here are the steps:

1. Set up your conditional formatting rules to color the cells based on your criteria. Give the rule a unique name like “RedCells”.
2. Create a named formula using Name Manager called “ColorRed” with this formula:

=GET.CELL(63,INDIRECT(“rc”,FALSE))=VLOOKUP(“RedCells”,$B$30:$C$35,2,0)

3. Enter your sum formula like:

=SUMIF(B2:B100,TRUE,B2:B100*ColorRed)

4. Copy the ColorRed named formula and SUMIF down their columns.

The GET.CELL retrieves the color of each cell, the VLOOKUP matches it to the named format “RedCells”, and the SUMIF totals the values in column B where the ColorRed formula returns TRUE.

To get the average of colored cells instead, use AVERAGEIF. For a count, use COUNTIF.

Pros and Cons of GET.CELL Formulas

The key advantage of this approach is that your formulas will automatically recalculate whenever cell colors change. As long as your conditional formatting rules stay consistent, you’ll always have up-to-date totals of each color.

The downside is that it takes some initial setup to create the named formulas and hook them up to your conditional formats. It’s also less flexible than filtering since your formulas are tied to specific color rules.

Method 3: Custom VBA Functions

For the most dynamic option that will automatically sum/average/count cell values based on colors without any conditional formatting or named formulas, you can use custom VBA functions.

Here are the steps:

1. Go to the Visual Basic Editor (Alt+F11)
2. Right-click your workbook name, select Insert > Module
3. Paste this code into the Module:

Function SumByColor(rng As Range, colorNum As Integer) As Double
  Dim total As Double
  Dim cell As Range
  For Each cell In rng
    If cell.Interior.Color = colorNum Then
        total = total + cell.Value
    End If
  Next cell
  SumByColor = total
End Function

4. Save and return to your Excel worksheet
5. Enter a formula like:

=SumByColor(B2:B100, RGB(255, 0, 0))

That formula will sum all values in red cells. The RGB function specifies the color to match. You can look up RGB color codes to find the one you need.

To get the average of colored cells with VBA instead of the sum, modify the custom function like this:

Function AverageByColor(rng As Range, colorNum As Integer) As Double
  Dim total As Double  
  Dim count As Integer
  Dim cell As Range
  For Each cell In rng
    If cell.Interior.Color = colorNum Then
        total = total + cell.Value
        count = count + 1
    End If
  Next cell
  AverageByColor = total / count
End Function

For a count of colored cells, use:

Function CountByColor(rng As Range, colorNum As Integer) As Double
  Dim count As Integer
  Dim cell As Range
  For Each cell In rng
    If cell.Interior.Color = colorNum Then
        count = count + 1  
    End If
  Next cell
  CountByColor = count
End Function

Then enter formulas like:

=AverageByColor(C2:C100, RGB(0, 255, 0))
=CountByColor(A2:A200, RGB(255, 255, 0))

Pros and Cons of VBA Functions

Using custom VBA functions to retrieve values by cell color is incredibly flexible and efficient. You don’t need to set up any conditional formatting, named ranges, or helper columns. Your formulas are compact and readable.

Plus, VBA gives you the full power of looping through each individual cell to check its color and value. So it will work with any coloring scheme, even if cells are colored manually or with multiple conflicting conditional format rules.

The only disadvantage is that you must enable macros in your workbook for the custom functions to run. And if you want to modify them or create your own, you’ll need some understanding of Excel VBA programming. But once set up, the formulas are simple to use.

Summary

To recap, here are the main ways to get values based on cell color in Excel:

The best approach will depend on your spreadsheet and how often you need to pull data from colored cells. But with these techniques, you can unlock lots of possibilities for dynamically extracting values based on color formatting in Excel.

Whether you need to analyze data, create reports, check thresholds, or summarize results, these formulas will let you quickly retrieve values from cells of a specific color. Experiment with each method to find the one that works best for your needs!

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.