Count by color

emsquali 

There are two main approaches to count the number of cells within a range based on their color:

1. Using the SUBTOTAL function with filters:

• Filter the range by color: Select the range D4:M8 and click the "Filter" button on the "Home" tab. In the "Filter by Color" section, select the color you want to count.
• Insert the SUBTOTAL function: Select an empty cell where you want to display the count and type the following formula:

=SUBTOTAL(9, D4:M8)

The 9 in the SUBTOTAL function indicates that you want to count all cells in the range, regardless of their formatting.

•  Adjust the formula for multiple colors: If you want to count more than one color, you'll need to create separate SUBTOTAL functions for each color. You can use the same formula structure, but replace the cell reference with the range of cells that match the specific color.

2. Using VBA (Visual Basic for Applications):

• Create a VBA module: Open the Excel file and press Alt + F11 to open the Visual Basic Editor. Right-click on the workbook name in the Project Explorer window and select "Insert > Module".
• Write the VBA code: Paste the following code into the module:

Function CountCellsByColor(targetRange As Range, targetColor As Range) As Integer
    ' Count the number of cells with the specified color
    Dim count As Integer
    count = 0
    For Each cell In targetRange
        If cell.Interior.ColorIndex = targetColor.Interior.ColorIndex Then
            count = count + 1
        End If
    Next cell
    CountCellsByColor = count
End Function

• Use the function in Excel: In your Excel file, enter the following formula into the cell where you want to display the count:

=CountCellsByColor(D4:M8, B5)

Replace B5 with the cell reference containing the color for which you want to count the cells.

Both methods will allow you to count cells based on their color. The SUBTOTAL function is simpler to use, while the VBA function is more flexible and can be used to count cells of multiple colors. AI was partially deployed to support the text.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.