Previous Topic Next Topic

How to sum row color

Closed
Pooja - Mar 3, 2016 at 05:46 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 3, 2016 at 11:44 AM
Hello,

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

1 reply

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Mar 3, 2016 at 11:44 AM
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

0