Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: sumif but only for cells in the range that are bold

  1. #11
    New Member
    Join Date
    May 2003
    Location
    London
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif but only for cells in the range that are bold

    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

  2. #12
    New Member
    Join Date
    May 2003
    Location
    London
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif but only for cells in the range that are bold

    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

  3. #13
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,381
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: sumif but only for cells in the range that are bold

    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)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #14
    New Member
    Join Date
    May 2003
    Location
    London
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif but only for cells in the range that are bold

    Howdy jmiskey,

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

    Cheers,

    Garry

  5. #15
    Board Regular
    Join Date
    Mar 2011
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif but only for cells in the range that are bold

    Quote Originally Posted by Joe4 View Post
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •