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

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 2

The worksheet formula, =CELL("color",D3) returns 1 if the cell is formatted with color 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