[Solved] Count Cells in a row based on Color

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 .

1690293279638.png

A7= Count cells in row 7 where Color is Yellow

Regards,
Faisal
 

Attachments

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.

Code:
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.

Code:
=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.
1-VBA Function in A7.png
  • 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.
2-VBA Function adjusting in A7.png

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.
 

Attachments

Last edited:
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.
 
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
 
Last edited:
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).
 
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,
 

Attachments

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.
 

Online statistics

Members online
0
Guests online
13
Total visitors
13

Forum statistics

Threads
371
Messages
1,623
Members
704
Latest member
Michael Mpofu
Back
Top