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

 

[lenze]

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

 

[Garry Bettle]

Howdy lenze,

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

Cheers,

Garry

 

[Garry Bettle]

Howdy All,

Sorry to resurrect this. Can anyone help?

Cheers,

Garry

 

[Joe4]

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)

 

[Garry Bettle]

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

 

[Joe4]

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.

 

[Garry Bettle]

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?

 

[Joe4]

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.

 

[Ekim]

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:

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