add up things by distinguishing them by color

[TheFlatFace]

I want to make some calculations. but the calculations are distinguished by color. I want to add them by color. like the example in the picture I added. 

description on image

[#2]

@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).

The code:

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:

1. Open the Visual Basic Editor (VBE)
   1. ALT+F11 should do the trick
   2. Or, click on Developer tab and click Visual Basic. If Developer tab not showing:
      1. Right-click the ribbon, choose Customize the Ribbon
      2. On the right list, check the box next to Developer
      3. Click OK
2. Find your project in the Project Explorer
   1. If not showing, press CTRL+R
3. Right-click your project, select Insert > Module
4. Paste the code in the code pane
5. Close the VBE

Usage:

=SumByFontColor(RangeToSum,CellWithFontColorToSumBy)

Example:

You want to sum range A1:A10. Cell B1 contains a cell which has the font color you want to sum by:

=SumByFontColor(A1:A10,B1)

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.