<p>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?</p>

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

[{'answer_id': 49983, 'author': 'Sean', 'author_id': 4919, 'author_profile': 'https://Stackoverflow.com/users/4919', 'pm_score': 3, 'selected': False, 'text': '<p>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:</p>\n\n<p><a href="http://www.cpearson.com/excel/colors.aspx" rel="nofollow noreferrer">http://www.cpearson.com/excel/colors.aspx</a></p>\n\n<p>Example form the site:</p>\n\n<blockquote>\n  <p>The SumColor function is a color-based\n  analog of both the SUM and SUMIF\n  function. It allows you to specify\n  separate ranges for the range whose\n  color indexes are to be examined and\n  the range of cells whose values are to\n  be summed. If these two ranges are the\n  same, the function sums the cells\n  whose color matches the specified\n  value. For example, the following\n  formula sums the values in B11:B17\n  whose fill color is red.</p>\n  \n  <p><code>=SUMCOLOR(B11:B17,B11:B17,3,FALSE)</code></p>\n</blockquote>\n'}, {'answer_id': 50462, 'author': 'Graham', 'author_id': 1826, 'author_profile': 'https://Stackoverflow.com/users/1826', 'pm_score': 3, 'selected': False, 'text': '<p>The worksheet formula, <code>=CELL("color",D3)</code> returns <code>1</code> if the cell is formatted with color for negative values (else returns <code>0</code>).</p>\n\n<p>You can solve this with a bit of VBA. Insert this into a VBA code module:</p>\n\n<pre><code>Function CellColor(xlRange As Excel.Range)\n    CellColor = xlRange.Cells(1, 1).Interior.ColorIndex\nEnd Function\n</code></pre>\n\n<p>Then use the function <code>=CellColor(D3)</code> to display the <code>.ColorIndex</code> of <code>D3</code></p>\n'}, {'answer_id': 18542074, 'author': 'Stan Towianski', 'author_id': 2366973, 'author_profile': 'https://Stackoverflow.com/users/2366973', 'pm_score': 2, 'selected': False, 'text': '<p>I just created this and it looks easier. You get these 2 functions:</p>\n\n<pre><code>=GetColorIndex(E5)  &lt;- returns color number for the cell\n</code></pre>\n\n<p>from (cell)</p>\n\n<pre><code>=CountColorIndexInRange(C7:C24,14) &lt;- returns count of cells C7:C24 with color 14\n</code></pre>\n\n<p>from (range of cells, color number you want to count)</p>\n\n<p>example shows percent of cells with color 14</p>\n\n<pre><code>=ROUND(CountColorIndexInRange(C7:C24,14)/18, 4 )\n</code></pre>\n\n<p>Create these 2 VBA functions in a Module (hit Alt-F11)</p>\n\n<p>open + folders. double-click on Module1</p>\n\n<p>Just paste this text below in, then close the module window (it must save it then):</p>\n\n<pre><code>Function GetColorIndex(Cell As Range)\n  GetColorIndex = Cell.Interior.ColorIndex\nEnd Function\n\nFunction CountColorIndexInRange(Rng As Range, TestColor As Long)\n  Dim cnt\n  Dim cl As Range\n  cnt = 0\n\n  For Each cl In Rng\n    If GetColorIndex(cl) = TestColor Then\n      Rem Debug.Print "&gt;" &amp; TestColor &amp; "&lt;"\n      cnt = cnt + 1\n    End If\n  Next\n\n  CountColorIndexInRange = cnt\n\nEnd Function\n</code></pre>\n'}, {'answer_id': 34723980, 'author': 'jsxt', 'author_id': 3627676, 'author_profile': 'https://Stackoverflow.com/users/3627676', 'pm_score': 2, 'selected': False, 'text': '<p>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 <a href="https://support.microsoft.com/kb/2815384" rel="nofollow">https://support.microsoft.com/kb/2815384</a>. 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:</p>\n\n<pre><code>Function CountBgColor(range As range, criteria As range) As Long\n    Dim cell As range\n    Dim color As Long\n    color = criteria.Interior.color\n    For Each cell In range\n        If cell.Interior.color = color Then\n            CountBgColor = CountBgColor + 1\n        End If\n    Next cell\nEnd Function\n</code></pre>\n'}, {'answer_id': 39443540, 'author': 'qoheleth', 'author_id': 2331190, 'author_profile': 'https://Stackoverflow.com/users/2331190', 'pm_score': 0, 'selected': False, 'text': '<p>Yes VBA is the way to go.</p>\n\n<p>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.</p>\n\n<p>Hitting \'Find All\' will give you the total number of cells found at the bottom left of the dialogue box.</p>\n\n<p><a href="https://i.stack.imgur.com/YZOG2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YZOG2.png" alt="enter image description here"></a></p>\n\n<p>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.</p>\n'}]
['https://Stackoverflow.com/questions/49964', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/453046/']