How to sum numbers in a range that only have a particular cell color [Excel]?

Question

I would like to put a formula in Excel that would sum all the numbers of a specific range that have a specific cell background color.

Answer 1

There is no way to return the exact color of a cell using built-in Excel functions, only if the cell is colored or not.

To do this you have to use a user defined function that can return the color of the cell, like this example. Another useful link is here which indicates even these user defined functions might have problems if the cell is colored via conditional formatting. Again, I recommend instead of coloring by hand or by conditional formatting to indicate some state, you make that state itself part of the data.

Perhaps a workaround is to consider what criteria you are using to apply formatting, and instead use that criteria for some sort of filter.

Answer 2

You can select all of the cells and do a sum. Here's how to select them:

Answer 3

Search the web for an Add-in called xCELLcolor. It exposes four helpful functions to Excel 2007-2013: Sum by background color, count by background color, sum by font color, and count by font color. No need for VBA or macros and no need to re-sort your data.