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.

1

3 Answers

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.

1

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

1

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.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy