CODEWITHSUNDEEP
excelfunctionexcel-formulaexcel-2013
lala_12
lala_12

Reputation: 131

Count number of cells with color fill Excel 2013

Is there a way/a function to count the number of color filled cells in Excel 2013 without using VBA? Something like 

COUNTxxx(A1:A20)

Upvotes: 1

Views: 1691

Answers (3)

Mesut Akcan
Mesut Akcan

Reputation: 897

Function CountColor(xRange As Range, xColor As Long) As Long
Dim xCell As Range
For Each xCell In xRange
    If xCell.Font.ColorIndex = xColor Then
        CountColor = CountColor + 1
    End If
Next
End Function

Use this function in excel

=CountColor(A1:E25;3)

3 = Red

Upvotes: 1

Alexander Frolov
Alexander Frolov

Reputation: 306

Unfortunately, it is not possible to fulfill this task using only the Excel built-in function(s).

There are 2 ways:

  1. Create one or several User Defined Functions using VBA that will count / sum colored cells and then call them from the workbook. You can also place these UDFs into the personal.xlsb file, then you don't need to copy them each time in a new workbook. However, it is not possible to correctly process cells colored with a conditional format using UDF.
  2. Use a special add-in that will count / sum the necessary cells based on their color.

You can find more details about the ways to count / sum colored cells, including the VBA code, in this blog post: How to count and sum cells by color in Excel

Upvotes: 2

David Andrei Ned
David Andrei Ned

Reputation: 809

Pretty sure you have to use VBA

Upvotes: 2

Related Questions