SumByColour displays #VALUE!

davesexcel. Thanks, I have uploaded an example file, cell H8 is were I am trying to sum by colour. If in cell H8 if you go to formulas>insert function you can see it is summing it up correctly but on the sheet there is a #VALUE! error.

Many thanks in advance for any help anyone could offer, thanks.

Example.xlsm

Thanks for your advice CK76, sorry I am a novice at this and have just been using google to get to where I am. I have tried your code and it hasn't worked. With the old code when i look at the result it displays the figure I want in the insert function but using the new code it displays 0 in the insert function.

Colours appear to be the same but these are conditionally formatted colours so not the true cell background colours which is why I used the displayformat.

Any further advice would be appreciated, thanks.

Try it like

Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = Evaluate("getcfcolour(" & SumColor.Address(, , , 1) & ")")
Set rCell = SumRange
For Each rCell In SumRange
If Evaluate("getcfcolour(" & rCell.Address(, , , 1) & ")") = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

Private Function GetCFColour(Cl As Range) As Long
   GetCFColour = Cl.DisplayFormat.Interior.ColorIndex
End Function

Fluff13, you are a genius!!!! Thank you so much this worked perfectly, it had been driving me crazy for weeks!!!

Hi Fluff13, one last thing the code works perfectly and sums the values and displays them correctly. The only thing is if something changes it doesn't automatically update and recalculate unless you go into the formula and press return as if you have just entered the formula again? Is there any workaround so it auto updates? Thank you.

You could add this line

Dim TotalSum As Long
Application.Volatile
SumColorValue = Evaluate("getcfcolour(" & SumColor.Address(, , , 1) & ")")

Hi Fluff13, the added line didn't work but not to worry, I can live with it. Many thanks for all of your help.

Glad to help & thanks for the feedback.