Coloured Cells/Contents

[MarcosM]

Hi Guys, bit a headscratcher, I have a roster that im trying to calculate.

The roster has several employees, each with there own colour and how many hours they have, eg, A1, A2 and A3 are Green and have 1 in each cell.

So lets say Alan, who is "green" will have a total of 3.

Is there a function to capture this automatically...?

 

[Fluff]

Hi & welcome to MrExcel.
How are the cells getting coloured?

 

[MarcosM]

Hi & welcome to MrExcel.
How are the cells getting coloured?

There just "filled" manually and then the number added..

 

[Fluff]

In that case have a look here https://trumpexcel.com/count-colored-cells-in-excel/
It shows various ways

 

[MarcosM]

In that case have a look here https://trumpexcel.com/count-colored-cells-in-excel/
It shows various ways

Thanks Fluff, im looking to sum the contents of the cells, rather than count how many colour cells there are, eg: red=€456 yellow=€594 etc?

 

[Michael M]

Maybe this...
Copy this into a standard module

Function CCI(rng As Range, iColor As Integer)
Dim rCell As Range, isum As Integer
isum = 0
For Each rCell In rng
    If rCell.Interior.ColorIndex = iColor Then
        isum = isum + rCell.Value
    End If
Next rCell
CCI = isum
End Function

Then highlight the range to sum and use the formula

=CCI(A1:F1000,3)

A1:F1000 being your cell range AND 3 ( currently red) for the colour code of your cells

 

[MarcosM]

Maybe this...
Copy this into a standard module

Function CCI(rng As Range, iColor As Integer)
Dim rCell As Range, isum As Integer
isum = 0
For Each rCell In rng
    If rCell.Interior.ColorIndex = iColor Then
        isum = isum + rCell.Value
    End If
Next rCell
CCI = isum
End Function

Then highlight the range to sum and use the formula

=CCI(A1:F1000,3)

A1:F1000 being your cell range AND 3 ( currently red) for the colour code of your cells

Thanks for the reply, I found the following and it worked a treat :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/comment-page-2/[/FONT]

 

[Michael M]

OK....as long as you got a result...