Count a list of cells with the same background color

Question

Each cell contains some text and a background color. So I have some cells that are blue and some that are red. What function do I use to count the number of red cells?

I have tried =COUNTIF(D3:D9,CELL("color",D3)) with no success (Where D3 is red).

Answer 1

I believe you'll have to use a custom function using VBA.

Here is an example. You probably can simply cut/past it into your spreadsheet's code section.

Answer 2

Excel has no way of gathering that attribute with it's built-in functions. If you're willing to use some VB, all your color-related questions are answered here:

http://www.cpearson.com/excel/colors.aspx

Example form the site:

The SumColor function is a color-based analog of both the SUM and SUMIF function. It allows you to specify separate ranges for the range whose color indexes are to be examined and the range of cells whose values are to be summed. If these two ranges are the same, the function sums the cells whose color matches the specified value. For example, the following formula sums the values in B11:B17 whose fill color is red.

=SUMCOLOR(B11:B17,B11:B17,3,FALSE)

Answer 3

The worksheet formula, =CELL("color",D3) returns 1 if the cell is formatted with colour for negative values (else returns 0).

You can solve this with a bit of VBA. Insert this into a VBA code module:

Function CellColor(xlRange As Excel.Range)
    CellColor = xlRange.Cells(1, 1).Interior.ColorIndex
End Function

Then use the function =CellColor(D3) to display the colorIndex of D3

Answer 4

Dear Friends,

Given site and formulas are good for nothing due one reason is they can work only once.

when you will close the sheet and you will reopen it is not going to work.

try to do it i am not liying.

these persons can modify the formula so that it can work like normal function in excel.

then it will work everytime when you open the sheet, untill then you have to wait nothing else.

Sory i dont want to hurt or say anything wrong about anyone.

http://www.excelansguru.blogspot.com