How to sum row color

[Closed]
Report
-
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
-
Hello,

Please help us with how to sum numbers marked in different colors.

1 reply

Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Pooja,

Paste the following code in a module (Alt+F11>insert Module>Paste code in big white field):
Function CountColorNumber(MyRange As Range, Color As Range) As Long
Dim MyColor As Long

MyColor = Color.Interior.ColorIndex

For Each cell In MyRange
    If cell.Interior.ColorIndex = MyColor Then
        CountColorNumber = CountColorNumber + cell.Value
    End If
Next cell
End Function


Then use the formula:
=CountColorNumber(Range of cells, the cell with the color)

So if range A1:A10 has different colors and you want to add the ones that are colored red, then color for example B1 red and type formula in B2:
=CountColorNumber(A1:A10,B1)


Best regards,
Trowa