This post will guide you how to sum only numbers with bold style in a range of cells in Excel. How do I sum on cells with bold font within a range of cells using User defined function in Excel 2013/2016/2019/365.
Suppose you have a column of data and only some of the numbers are bolded. You want to create a formula that sums only the bolded numbers. Where do you want to start? This post will work it out for you. The approach will be divided into several steps, just follow them and let’s get started!
Table of Contents
1. Sum Bold Numbers
It is difficult to use some common combinations of functions to calculate bolded numbers. It is better to use user defined functions to do this.
Assuming that you have a list of data in range B1:B6, which contain some bold or normal numbers. and you wish to sum only numbers with bold font style, how to quickly sum those number based on bold font style in the given range of cells in Excel. And this tuotrial will show you how to sum only the bold numbers with a User Defined Function in your worksheet. Just do the following steps:
Step1# open your excel workbook and then click on Visual Basic command under DEVELOPER Tab, or just press ALT+F11 shortcut.
Step2# then the Visual Basic Editor window will appear.
Step3# click Insert ->Module to create a new module.
Step4# paste the below VBA code into the code window. Then clicking Save button.
Function SumOnlyNumbersBold(myRange As Range) For Each myCell In myRange If myCell.Font.Bold Then mySum = mySum + myCell.Value End If Next SumOnlyNumbersBold = mySum End Function
Step5# back to the current worksheet, then type the following formula in a blank cell , and then press Enter key.
For example, if you want to sum the bold numbers in range A2:A10, you can use the following formula: type equals SumOnlyBoldNumbers, select A2 to A10.
2. Sum Only Bold Numbers by SUMIF Function (Also Use VBA)
If you find the first method difficult to understand. You can create a simple formula through VBA (User Defined Function) to determine whether a cell is formatted in bold.
You can refer the above steps to create a VBA Module to insert the following VBA code.
Function IsCellBold(rng As Range) As Boolean IsCellBold = rng.Font.Bold End Function
Then enter the following formula to check if cell A2 is bold in B2.
Then Drag down the above formula in Cell B2.
You will get a new list, consisting of true and false, about whether the range A2:10 contains some bold numbers.
Now, you can use the SUMIF function to sum numbers based on the criteria true and false. Enter the formula:
This function only sums the bolded numbers whose adjacent cells are logical TRUE.
3. Video: Sum Only Numbers in Bold
This video will demonstrate you how to sum only numbers with bold style in a range of cells in using User defined function in Excel 2013/2016/2019/365.