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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I just created this and it looks easier  You get these 2 functions    GetColorIndex E5    lt - returns color number for the cell   from  cell    CountColorIndexInRange C7 C24 14   lt - 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   gt    amp  TestColor  amp    lt         cnt   cnt   1     End If   Next    CountColorIndexInRange   cnt  End Function

User · Answer

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

[excel] Count a list of cells with the same background color

Examples related to excel

Examples related to vba