# Thread: sumif but only for cells in the range that are bold Thanks: 0 Likes: 0

1. ## 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. ## 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. ## 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. ## Re: sumif but only for cells in the range that are bold

Howdy All,

Sorry to resurrect this. Can anyone help?

Cheers,

Garry

5. ## 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)

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

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

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

#### Posting Permissions

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