Excel: Sum only cells that are in bold

There is no such function in Excel and to workaround this problem you have two options:

• help yourself using VBA (solution from here),
• consider using a plugin (spending 39 USD), i.e. Kutools for Excel.

Here, I’ll discuss first option.

Base solution

Code needed to solve the problem in title is as easy as:

Function SumIfBold(MyRange As Range) As Double

    Dim currentCell As Range

    For Each currentCell In MyRange
        If currentCell.Font.Bold = True Then
            SumIfBold = SumIfBold + currentCell
        End If
    Next currentCell

End Function

And then put =SumIfBold(A1:A100) or something similar into any cell.

You can easily change / extend this function to vary on different cell’s format.

Conditional summing

I’m not a VBA ninja. Moreover, I’m rather very beginner to this area. Thus, for summing only if cell is in bold and has value higher or lower than zero I came only with something like this…

Function SumIfBoldPositiveOnly(MyRange As Range) As Double

    Dim currentCell As Range

    For Each currentCell In MyRange
        If currentCell.Font.Bold = True Then
            If currentCell > 0 Then SumIfBoldPositiveOnly = SumIfBoldPositiveOnly + currentCell
        End If
    Next currentCell

End Function

…and like this:

Function SumIfBoldNegativeOnly(MyRange As Range) As Double

    Dim currentCell As Range

    For Each currentCell In MyRange
        If currentCell.Font.Bold = True Then
            If currentCell < 0 Then SumIfBoldNegativeOnly = SumIfBoldNegativeOnly + currentCell
        End If
    Next currentCell

End Function

Sheet recalculation

Keep in mind that in this particular example we’re summing cells only if their formatting is bold. Changing formatting of a cell (i.e. pressing Ctrl + B) isn’t an event that can trigger sheet recalculation (at least in Excel — it is rumored that there is such event in Access).

In other words, you can change style (bolding) of your cells that must be summarized and value in resulting cell (i.e. the one where you put something similar to =SumIfBold(A1:A100) won’t change. Neither automatically nor manually (i.e. after pressing F9).

To workaround this you need to provide some code for Worksheet_SelectionChange trigger.

The simplest and ugliest one is:

If Not Target Is Nothing Then
    Me.Calculate
End If

Double click VBAProject > Microsoft Excel Objects > [sheet name], select Worksheed and SelectionChange from the dropdown menus and paste above code. Save changes and return to your sheet to see effects.

With above code you should have corresponding (sum) cell refreshed each time you change some cell’s formatting to bold or not bold and you move your selection to somewhere else (i.e. to the next cell etc.).