sumif but only for cells in the range that are bold

Garry Bettle

New Member
Joined
May 29, 2003
Messages
8
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Garry Bettle

New Member
Joined
May 29, 2003
Messages
8
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,425
Office Version
365
Platform
Windows
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)
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
122
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,261
Messages
5,467,633
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top