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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
What makes the cells bold? Are they formatted that way, are do they become bold via conditional formatting?
 
Upvote 0
Howdy lenze,

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

Cheers,

Garry
 
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
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