Formula to Sum a Column by Fill Colour

[stuartgb100]

Hi,

I have col C containing numbers.
Some cells in col C have a pink fill colour RGB 252:228:214
Some cells in col C have a blue fill colour RGB 221:235:247

In cell H3 I'd like to place a formula that will be the total of all pink cells in col C.
Similarly in J3 a formula for the total of all blue cells in col C.

Can I do this please ?

For better presentation, I'd rather not use two columns (one for each colour).

Thanks.

 

[AliGW]

Stuart - so glad it worked for you. Obviously you can extend the ranges to suit. Yes, small world - I'm in IP4, so the east side of Ipswich.

 

[stuartgb100]

Blimey, you're nearly in the sea !!!

I'm east of Cambridge.

Best Regards and thanks again for all the help.

 

[AliGW]

Yes - not far from the great Orwell River!

 

[Guzzlr]

stuartgb100,

How about something like this?

Function SumByColor(CellColor As Range, SumRange As Range)
' SumByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 10/30/2016 by hiker95
' =SumByColor(H3,C:C)
' =SumByColor(J3,C:C)
' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + myCell.Value
  End If
Next myCell
SumByColor = myTotal
End Function

The above code is unfortunately not working either. I still have to click up in the formula bar to reset the colors.
I did run the Macro recorder and this is what it came up with:

Sub Macro1()'
' Macro1 Macro
'


'
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=SumByColor(R[6]C,R[3]C:R[28]C)"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=SumByColor(R[6]C,R[2]C:R[27]C)"
    Range("A1").Select
End Sub

This sort of works, it mimics me clicking on H4 & H5 where my formula is and clicking on the formula bar and then on the sheet to reset the formula's in H4 & H5