CODEWITHSUNDEEP
excelvba
FortunateDuke
FortunateDuke

Reputation: 1153

Count a list of cells with the same background color

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).

Upvotes: 10

Views: 193809

Answers (5)

qoheleth
qoheleth

Reputation: 2309

Yes VBA is the way to go.

But, if you don't need to have a cell with formula that auto-counts/updates the number of cells with a particular colour, an alternative is simply to use the 'Find and Replace' function and format the cell to have the appropriate colour fill.

Hitting 'Find All' will give you the total number of cells found at the bottom left of the dialogue box.

enter image description here

This becomes especially useful if your search range is massive. The VBA script will be very slow but the 'Find and Replace' function will still be very quick.

Upvotes: 0

jsxt
jsxt

Reputation: 1135

I was needed to solve absolutely the same task. I have divided visually the table using different background colors for different parts. Googling the Internet I've found this page https://support.microsoft.com/kb/2815384. Unfortunately it doesn't solve the issue because ColorIndex refers to some unpredictable value, so if some cells have nuances of one color (for example different values of brightness of the color), the suggested function counts them. The solution below is my fix:

Function CountBgColor(range As range, criteria As range) As Long
    Dim cell As range
    Dim color As Long
    color = criteria.Interior.color
    For Each cell In range
        If cell.Interior.color = color Then
            CountBgColor = CountBgColor + 1
        End If
    Next cell
End Function

Upvotes: 2

Stan Towianski
Stan Towianski

Reputation: 409

I just created this and it looks easier. You get these 2 functions:

=GetColorIndex(E5)  <- returns color number for the cell

from (cell)

=CountColorIndexInRange(C7:C24,14) <- returns count of cells C7:C24 with color 14

from (range of cells, color number you want to count)

example shows percent of cells with color 14

=ROUND(CountColorIndexInRange(C7:C24,14)/18, 4 )

Create these 2 VBA functions in a Module (hit Alt-F11)

open + folders. double-click on Module1

Just paste this text below in, then close the module window (it must save it then):

Function GetColorIndex(Cell As Range)
  GetColorIndex = Cell.Interior.ColorIndex
End Function

Function CountColorIndexInRange(Rng As Range, TestColor As Long)
  Dim cnt
  Dim cl As Range
  cnt = 0

  For Each cl In Rng
    If GetColorIndex(cl) = TestColor Then
      Rem Debug.Print ">" & TestColor & "<"
      cnt = cnt + 1
    End If
  Next

  CountColorIndexInRange = cnt

End Function

Upvotes: 4

Graham
Graham

Reputation: 15234

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

Upvotes: 6

Sean
Sean

Reputation: 4670

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)

Upvotes: 10

Related Questions