Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
3  
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
add comment

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.

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
add comment

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
add comment

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
1  
Note that this is a commercial solution provided by FullScope Solutions. –  dangowans Sep 16 '12 at 14:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.