You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter mfaisal.ce
- Start date

Hello Faisal,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 .

To count cells in row

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

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

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

Regards.

Last edited:

Thanks Dear

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

regards,

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

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:

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:

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.

Use SUMPRODUCT to sum values in Column A where the font color matches your target color:

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).

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.