04-23-2020 02:12 AM
04-24-2020 09:36 AM
@TheFlatFace hello there,
Unfortunately you can't do this with native functions. You can set a filter range on your data and filter by color - which is now available in Excel Online as well.
That being said, there is a VBA solution. You would need to install this code into your workbook and it would only work there (other solutions exist to make it available in other workbooks if you'd like).
Public Function SumByFontColor( _ ByVal SumRange As Range, _ ByVal FontColorCell As Range _ ) As Double Dim Cell As Range Dim SourceColorIndex As Long SourceColorIndex = FontColorCell.Font.ColorIndex For Each Cell In SumRange If Cell.Font.ColorIndex = SourceColorIndex And IsNumeric(Cell.Value) Then SumByFontColor = SumByFontColor + Cell.Value End If Next Cell End Function
To install the code:
You want to sum range A1:A10. Cell B1 contains a cell which has the font color you want to sum by:
Note this formula will not update itself if you change cell colors. That action doesn't trigger an update in the calc tree of any kind. You could add "Application.Volatile = True" to the function but that would only help in limited cases. This is not perfect solution.
As a side note, I would recommend not using things like "222k" in a cell as a value, as Excel will see that as text and not be able to do math on it.