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

#### Garry Bettle

##### New Member
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 PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### lenze

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

#### Garry Bettle

##### New Member
Howdy lenze,

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

Cheers,

Garry

#### Garry Bettle

##### New Member
Howdy All,

Sorry to resurrect this. Can anyone help?

Cheers,

Garry

#### Joe4

##### MrExcel MVP, Junior Admin
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)

#### Garry Bettle

##### New Member
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

##### MrExcel MVP, Junior Admin
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

##### New Member
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

##### MrExcel MVP, Junior Admin
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

##### Well-known Member
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

1,102,020
Messages
5,484,222
Members
407,435
Latest member
scottsweigart1

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...