sumif but only for cells in the range that are bold

[Garry Bettle]

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

 

[Garry Bettle]

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

 

[Garry Bettle]

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]

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

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)

 

[Garry Bettle]

Howdy jmiskey,

As Mr. Sparks would say, "I like it!"

Cheers,

Garry

 

[cgsierra]

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:

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