Calculating on cell background colour

[asokaw]

Is there any formula in excel to get the sum of specific cells by its background cell color.

Example

I need to get the sum of cell values which have Orange backgroundg color ( eg. 100, 400 & 300 )
Return value should be 800.

100 200 500 400

300 50

 

[Rick Rothstein]

Is there any formula in excel to get the sum of specific cells by its background cell color.

How did the cells get their background color... manually set or conditional format?

 

[asokaw]

How did the cells get their background color... manually set or conditional format?

Using Conditional formatting

also tell me how to do it if its coloured manually

Rgds

 

[Rick Rothstein]

Using Conditional formatting

also tell me how to do it if its coloured manually

You cannot do it with a formula if manually colored (you would need VBA code). In order to try and do it with a worksheet formula, we need to know what formula you used for the conditional formatting and what color it produced.

 

[asokaw]

Lets say all cells where value is > 200 are in red colour & we need to add the red coloured cells..

 

[Rick Rothstein]

Lets say all cells where value is > 200 are in red colour & we need to add the red coloured cells..

Then you would use this formula (here I am assuming the overall range containing your red cells is A1:D9)...

=SUMIF(A1:D9,">200")

The point is, you do not need to know the color, only the relationship that made the cell that color... knowing that, you should be able to construct a formula that sums values meeting that condition. (I'm going to sleep now, so I won't be back to this thread for a few hours.)

 

[asokaw]

Thanks Rick, Have a good sleep