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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
What makes the cells bold? Are they formatted that way, are do they become bold via conditional formatting?
 

Garry Bettle

New Member
Joined
May 29, 2003
Messages
8
Howdy lenze,

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

Cheers,

Garry
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
May 29, 2003
Messages
8
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
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
May 29, 2003
Messages
8
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
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
Jun 30, 2002
Messages
1,416
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top