Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Always consider how you can restructure your data before trying to fix the analysis process. You are better off taking the information that you are encoding as a color and making it explicit in another column or something equivalent. – mindless.panda Mar 18 '10 at 19:22

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.

share|improve this answer
I was going to say this exact same thing, but then I saw rlb.usa demonstrate provably that you can indeed base a sort on the color of the cell. Color me impressed! – Nathan DeWitt Mar 18 '10 at 20:15

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

In Excel 2007

share|improve this answer
The order of the cells cannot be changed, but thanks anyway. – jason Mar 18 '10 at 21:31

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.

share|improve this answer
Note that this is a commercial solution provided by FullScope Solutions. – dangowans Sep 16 '12 at 14:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .