sumif but only for cells in the range that are bold

Garry Bettle

New Member
Joined
May 29, 2003
Messages
8
Howdy all,

Great site! Quick question about how to reference a bold format of a cell. Currently, I'm able to sumif as follows:

=SUMIF(A2:A183,1,$I$2:$I$183)

But, what if I wanted to do the above, but only the cells with a BOLD formatted 1? Plus, could I make the condition generic - sumif if the cell was a bold number and\or character - i.e. M? Many thanks.

Cheers,

Garry
 
Howdy Mike\Jmiskey,

Mike, that is it! Big thanks to both Mike\Jmiskey. However, just one more question:

If I have several columns to sumbold() (for example columns A-H) and a fixed column to sum (say column I), the offset approach doesn't quite cut the mustard, as I'd have to have a different offset per column. So, does this make it any easier, a fixed column to sum?

Sorry, for the hassle - VFP is my actually forte. Many thanks.

Cheers,

Garry
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sorted!

Thanks again everybody. All I changed was:

SumIfBold = SumIfBold + cell.Offset(0, 1)

to:

SumIfBold = SumIfBold + Cells(cell.Row, 9)

9 being the the fixed sum column I.

Cheers,

Garry
 
Upvote 0
If you want to make the formula more generic so you don't need to keep changing the macro, try this:

Code:
Function SumIfBold(MySumRange As Range, MyBoldColumnOffset As Integer) As Double

    Dim cell As Range
    For Each cell In MySumRange
        If cell.Offset(0, MyBoldColumnOffset).Font.Bold = True Then
            SumIfBold = SumIfBold + cell.Value
        End If
    Next cell

End Function

Basically, you enter in the range you want to sum, and the relative position of the column you are checking for bolding compared to the SUM column.

For example, let's say the values you want to sum are in the Range I1:I100, and the column you want to check for bolding is column A. Column A is 8 columns before column I, so use the formula:

=SUMIFBOLD(I1:I100,-8)
 
Upvote 0
I am not certain, bit I don't think you can do it with the SUMIF function. However, you can write a custom function in VBA to do it. Here is the code:

Code:
Function SumIfBold(MyRange As Range) As Double

    Dim cell As Range
    For Each cell In MyRange
        If cell.Font.Bold = True Then
            SumIfBold = SumIfBold + cell
        End If
    Next cell

End Function

Then, you would simply use it like a function on your spreadsheet. For example, if you wanted to sum up the bolded items in range A1 to A100, simply use the formula:

=sumifbold(A1:A100)

Hi, this I a great function but it doesn't seem to work with conditional formatted bold numbers. Can this be adjusted t work with numbers that are bold because of conditional formatting?
thank you
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top