[Solved] Count Cells in a row based on Color

[mfaisal.ce]

Dears,

I need to calculate number of cells in a row based on Color. Kindly guide me how to do. It is in a row not in a column .



A7= Count cells in row 7 where Color is Yellow

Regards,
Faisal

 

[Yousuf Shovon]

I need to calculate number of cells in a row based on Color. Kindly guide me how to do. It is in a row not in a column .

Hello Faisal,

To count cells in row 7 where the Fill Color is Yellow, you can define a custom function CountCellsByColor in VBA. The purpose of this function will be to count the number of cells in the specified range rng that have the same fill color as the cell referenced in color. Also, to make sure that the function recalculates whenever there is a change in the worksheet, use Application.Volatile. Follow the below steps to implement the VBA function:

• Copy the below VBA code to a module. Then, close the Visual Basic window.

Public Function CountCellsByColor(rng As Range, color As Range) As Long
    Dim count As Long
    Dim cell As Range
    Dim colorValue As Long

    Application.Volatile

    colorValue = color.Interior.color

    For Each cell In rng
        If cell.Interior.color = colorValue Then
            count = count + 1
        End If
    Next cell

    CountCellsByColor = count
End Function

• Now, enter the below VBA function in A7 >> press Enter key.

=CountCellsByColor(7:7,C7)

• Here, 7:7 indicates the whole row 7 and C7 denotes the interior cell color to count. In this case, C7 color is Yellow.

​

• Consequently, we obtain 2 as the total number of yellow-colored cells in row 7. You can add more colored cells and the function will adjust automatically.

​

Tips: Go to Change Cell Color Based on Text, to highlight your important data quickly and make your workbook more dynamic.

I am attaching the workbook. Try the code and let me know if it works for you.

Regards.

 

[mfaisal.ce]

Thanks Dear

 

[mfaisal.ce]

Hello,

Is it possible without VBA coding? means excel dont have any method to count cells based on colour?

regards,

 

[shamimarita]

Hello Faisal,

Currently, Excel doesn't have a built-in function to count cells based on color without using VBA. However, the VBA solution provided is a great way to achieve this functionality.
If you're looking for alternatives, consider using conditional formatting to visually track your colored cells.

 

[mfaisal.ce]

I have read on internet that Get.Cell(24,[cell_address]. it will give u color no . Is ther any way to use this function and then calcuate sum.

Suppose,
Column A has values with different Font colours.
Column B will give colour no based on Font colour in Column A using Get.cell(24,[Cell address]) as namerange defined.
Then, i can sum Column A values using sumif formula.

Now, problem for me that i dont want an additional column B which will only give the colour no based on Namerange function defined. Is there any way to use sumif formula with namerange without extra column B.



regards

 

[shamimarita]

Hello Faisal,

To calculate the sum of values in Column A based on font color without an additional column, you can try this approach with a defined name range and the SUMPRODUCT function in Excel. Here’s a step-by-step solution:

Define a Named Range Using GET.CELL:
Since GET.CELL is an old macro function, you’ll need to define it within a named range:

• Go to Formulas >> Name Manager > New.
• Name the range (e.g., FontColorCode).
• In the Refers to box, use this formula:
• =GET.CELL(24, A1)
  
  • Adjust A1 as needed to the first cell in your range.

Calculate the Sum Based on Color Using SUMPRODUCT:
Use SUMPRODUCT to sum values in Column A where the font color matches your target color:
=SUMPRODUCT((FontColorCode=target_color_code)*A:A)
Replace target_color_code with the specific color code you want to filter for.

This approach lets you avoid creating a helper column, but remember that GET.CELL will only update with a manual refresh (e.g., entering the formula again).

 

[mfaisal.ce]

Dear,
Thanks for the help. i tried but unable to succeed. can u plz check the file.
Aim is to calculate Red colour values seperate and Black colour values seperate in a column.

regards,

 

[shamimarita]

Thank you for sharing the file. I can certainly provide guidance on using the Name Manager option or other potential workarounds, but implementing a complete solution would usually require VBA due to the complexity of the task.
Since this type of customization goes beyond Excel’s built-in functions, I encourage trying the suggested steps, and I’d be happy to answer any questions along the way.