Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

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

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

    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

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    What makes the cells bold? Are they formatted that way, are do they become bold via conditional formatting?

  3. #3
    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 lenze,

    There is no conditional formatting - the cells are currently made bold manually (until I use conditionally formatting!).

    Cheers,

    Garry

  4. #4
    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 All,

    Sorry to resurrect this. Can anyone help?

    Cheers,

    Garry

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,744
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

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

    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)
    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!"

  6. #6
    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

    Thanks!

    However, could you help me a bit more? How would I change your code to sum a different column\range otherthan the bold one? Thanks again.

    Cheers,

    Garry

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,744
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

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

    I am not sure I understand what you are asking for. I thought what you wanted to do was this; sum up all the bold entries in a given range. That was what I wrote the macro for.

    So, for example, if in the range A1:A100, only cells A4, A25, A60, and A100 are bold, then =SUMIFBOLD(A1:A100) will only sum up those bold cells (A4, A25, A60, and A100).

    You can change the range to any range you want, and it will only sum up the bold entries in that range.
    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!"

  8. #8
    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

    Sorry jmiskey!

    Nothig is wrong with your code. Essentially, the bold cells refer to a row that has been selected, with the rows' right-most column the count column, not the column which is in bold. If I could re-write your code somehow to accept two parameter ranges, one to check\identify the bold cell and another to sum the values of it would be perfect!

    Cheers,

    Garry

    PS: I could send a sample xls if you like?

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,744
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

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

    I still am unsure of what it is you are asking. Please post an example. You can use "Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.", found at the bottom of the page, to display a section of your worksheet, if you like. If you are unable to do that, just work out an example as best as you can, giving as much detail as possible.
    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!"

  10. #10
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Jmiskey,

    Nice function.
    ...the bold cells refer to a row that has been selected, with the rows' right-most column the count column, not the column which is in bold...
    I think the OP wants to sum data in say column B if the respective cells in Column A are bold. If so:
    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.Offset(0, 1)
            End If
        Next cell
    
    End Function
    Notes:
    If “MyRange” is in column A, then Offset(0,1) refers to column B. If the “ rows' right-most column” is say column D, then change the offset to (0,3).

    Regards,

    Mike

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
  •  

 

DMCA.com