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.
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.
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
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
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
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.).
One comment on “Excel: Sum only cells that are in bold”
this is perfect, but I can only get the function to work on the workbook that it is stored in. I need the function to be stored in workbook A and utilized in workbook B. The workbook names will always be different, so I’m unable to use a constant name in any code.