Sum Column Based on Cell Color
 

Excel 2007

Is there a way to sum a column of numbers based on the background color of
the cells? For example, I have a column that contains gold, blue, and green
cells, and I want to sum only the green cells. The color definition is:

Red 153
Green 204
Blue 0


--
Thanks for any help that you can offer!

--Tom

Answer: Sum Column Based on Cell Color
 

Yes, it is possible to sum a column of numbers based on the background color of the cells in Excel 2007. Here are the steps to do so:

1. Select the cell where you want to display the sum of the green cells.
2. Click on the "Formulas" tab in the ribbon.
3. Click on the "More Functions" dropdown menu and select "Statistical".
4. Select "SUMIF" from the list of functions.
5. In the "SUMIF" dialog box, enter the range of cells you want to evaluate in the "Range" field.
6. In the "Criteria" field, enter the formula
   Formula:

   ---

   =CELL("color",A1)=204 


   ---

   , where "A1" is the first cell in the range you want to evaluate. This formula checks if the cell background color is green (which has a color code of 204).
7. In the "Sum_range" field, enter the range of cells you want to sum if the criteria is met.
8. Click "OK" to close the dialog box and display the sum of the green cells in the selected cell.

Note that this formula only works for cells with a solid fill color. If the cells have a pattern or gradient fill, this formula will not work. Also, if you want to sum cells with a different color, you will need to change the color code in the formula accordingly.

Sum Column Based on Cell Color
 

Sum based on the same criteria as how the cells became colored.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Thomas M." wrote in message
...
Excel 2007

Is there a way to sum a column of numbers based on the background color of
the cells? For example, I have a column that contains gold, blue, and
green
cells, and I want to sum only the green cells. The color definition is:

Red 153
Green 204
Blue 0


--
Thanks for any help that you can offer!

--Tom

Sum Column Based on Cell Color
 

The cells were colored manually. Each cell is a grand total for a
corresponding table. I figure that I can sum only the grand total numbers
from each table by either reworking the spreadsheet and using Excel's
subtotal feature, or by adding a column that would include only those grand
total numbers. But when I was looking at the problem I had this idea of
doing a conditional sum based on the cell color, and I just got curious as to
whether or not that can be done.
--
Thanks for any help that you can offer!

--Tom


"Don Guillett" wrote:

Sum based on the same criteria as how the cells became colored.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Thomas M." wrote in message
...
Excel 2007

Is there a way to sum a column of numbers based on the background color of
the cells? For example, I have a column that contains gold, blue, and
green
cells, and I want to sum only the green cells. The color definition is:

Red 153
Green 204
Blue 0


--
Thanks for any help that you can offer!

--Tom

Sum Column Based on Cell Color
 

Bob Phillips shows ways of doing it he

http://www.xldynamic.com/source/xld.ColourCounter.html

Hope this helps.

Pete

On Sep 2, 1:12*am, Thomas M.
wrote:
The cells were colored manually. *Each cell is a grand total for a
corresponding table. *I figure that I can sum only the grand total numbers
from each table by either reworking the spreadsheet and using Excel's
subtotal feature, or by adding a column that would include only those grand
total numbers. *But when I was looking at the problem I had this idea of
doing a conditional sum based on the cell color, and I just got curious as to
whether or not that can be done.
--
Thanks for any help that you can offer!

--Tom



"Don Guillett" wrote:
Sum based on the same criteria as how the cells became colored.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Thomas M." wrote in message
...
Excel 2007

Is there a way to sum a column of numbers based on the background color of
the cells? *For example, I have a column that contains gold, blue, and
green
cells, and I want to sum only the green cells. *The color definition is:

Red * * 153
Green *204
Blue * * * *0

--
Thanks for any help that you can offer!

--Tom- Hide quoted text -

- Show quoted text -

Sum Column Based on Cell Color
 

Excel 2007
Identify all 256^3 RGB colors,
not just the 56 index colors
http://www.mediafire.com/file/zmqlnzkmnwz/09_01_09.xlsm

Sum Column Based on Cell Color
 

Hi Thomas,

If you create the following User Defined Function (UDF), by entering Alt + F11

paste the following code into a Module:

Function Color(cell)
Color = cell.Interior.Color
End Function

Column with colored cells A2:A100, insert a helper column into column B,
then in cell B2:

=color(A2)

copy the formula from B2 to B3:B100.

=sumif(B2:B100,B2,A2:A100)

note, the color number for Red 153, Green 204, Blue 0 is 52377

so your formula could be:
=sumif(B2:B100,52377,A2:A100)

OR

You can also use the autofilter to filter by color. When data is filtered,
then you can use the subtotal worksheet function:

=SUBTOTAL(9,A:A)

--
If this post helps click Yes
---------------
Peggy Shepard


"Thomas M." wrote:

Excel 2007

Is there a way to sum a column of numbers based on the background color of
the cells? For example, I have a column that contains gold, blue, and green
cells, and I want to sum only the green cells. The color definition is:

Red 153
Green 204
Blue 0


--
Thanks for any help that you can offer!

--Tom