Sum By Colour

[sigmundo76]

I want to be able to sum the different colours in my spreadsheet.
The colours are decided by conditional formatting on the cell, so these colours will change when different data is entered.
Is there a formula that will count the coloured cells only?

I have had a look but can only find answers suggesting VBA is the way to go. This is a touch over my head at the minute though.

[dilipandey]

Hi sigmundo,

A quick way would be to filter by color and use Subtotal function to sum, see attached:-

sum by color.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[dredwolf]

There is NO formula that will count colours, if you use what you use for the conditional formatting as parameters for a COUNTIF/S function, you may be able to work with that, BUT COLOURS are not data, they are formatting (ie- what you see, not what is), if you need to do colour counting/calculations, you probably need to use VBA....

[sigmundo76]

What I need to do is count the numbers in the coloured cells as in the example from Dilipandey.
How do i tell Excell which colours to look for?
I have a total of 4 different colours
Also, not all cells in the relevant column are coloured the same.
So I may just want to count the red cells.

[fredrs05]

Here is a VBA alternative:

Use the function "SUMOFCOLOR"

[sigmundo76]

That's exactly it Fred.

How do I implement it into my workbook though?

I got a #NAME error.

Does it make a difference if the cells are coloyred when they meet certain conditions set in conditional formatting?

[fredrs05]

That's exactly it Fred.

How do I implement it into my workbook though?

Function SUMOFCOLOR(rCol As Range, iCol As Range)

    Dim Cell As Range
    
    A = iCol.Interior.ColorIndex
    cnt = 0

    For Each Cell In rCol
        If Cell.Interior.ColorIndex = A Then
            cnt = cnt + Cell.Value
        End If
    Next Cell
    
    SUMOFCOLOR = cnt
    
End Function

1)Copy the sample code above
2)Open the workbook in which you want to add the code
3)Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4)Choose Insert | Module
5)Where the cursor is flashing, choose Edit | Paste

Does it make a difference if the cells are coloyred when they meet certain conditions set in conditional formatting?

I'm not sure I understand what you mean.

The function SUMOFCOLORS will look at the current color on the selected cells.

Hope that helps.

[Kevin UK]

Hi sigmundo76

Use your conditional formatting. For example if you have conditional formatted column A to highlight numbers less <50. Use =SUMIF(A:A,"<50") to sum the cells which are coloured.

[fredrs05]

Hi sigmundo76

Use your conditional formatting. For example if you have conditional formatted column A to highlight numbers less <50. Use =SUMIF(A:A,"<50") to sum the cells which are coloured.

This is probably the best alternative, Sigmund.