Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Condition on chracter or cell format

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Marco
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I want to apply a condition (to sum up a column of numbers, for example) based on character format (for example, whether the numbers are bold or not).
    Which condition shall I use? (font.fontstyle= "bold"?). It does not work!

    The function to be used is :
    SOMMA.SE (in Italian) with the following parameters:
    Range on which the condition apply
    Condition
    Range to sum up.

    Such a function works perfectly well if I use a condition based, for example, on the value (for example, ">10000"). And if the condition is a cell format?


    Bruno

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-13 03:21, bam2000 wrote:
    Hi,

    I want to apply a condition (to sum up a column of numbers, for example) based on character format (for example, whether the numbers are bold or not).
    Which condition shall I use? (font.fontstyle= "bold"?). It does not work!

    The function to be used is :
    SOMMA.SE (in Italian) with the following parameters:
    Range on which the condition apply
    Condition
    Range to sum up.

    Such a function works perfectly well if I use a condition based, for example, on the value (for example, ">10000"). And if the condition is a cell format?


    Bruno
    What is the condition that you use to make a numeric cell bold, if you have one?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bruno

    Would code like this help you? You have to select your range of cells before running the macro ,or change first line to eg - For each cell in Range("B2:B19"). It gives the result both in cell D1 and as a message box, change to meet your circumstances.

    For Each cell In Selection
    If cell.Font.Bold = True Then
    Count = Count + cell.Value
    End If
    Next
    MsgBox "" & Count
    Range("D1").Value = Count
    End Sub

    regards
    Derek

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Marco
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-13 06:16, Derek wrote:
    Hi Bruno

    Would code like this help you? You have to select your range of cells before running the macro ,or change first line to eg - For each cell in Range("B2:B19"). It gives the result both in cell D1 and as a message box, change to meet your circumstances.

    For Each cell In Selection
    If cell.Font.Bold = True Then
    Count = Count + cell.Value
    End If
    Next
    MsgBox "" & Count
    Range("D1").Value = Count
    End Sub

    regards
    Derek
    Hi Derek,
    I do not want to use VBA for solving my problem, because I am sure that there is the possibility to use the buillt-in function SOMMA.SE (SUM.IF), merely substituting the condition (see the Excel Help) with a condition which takes into account the format of the cell (in my case the bold appearence of the numbers I want to sum up).
    To better explain, a column of numerical data has a subset in bold font and the remaining data in normal font.
    I want to sum up only those in bold.
    Thank for your replay
    Bruno















































    0










  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bruno
    The only way I know is by creating a UDF (User Defined Function). Paste the following function code into an empty module

    Function SumBold(Arg As Range)
    Application.Volatile
    Count = 0
    For Each cell In Arg
    If cell.Font.Bold = True Then
    Count = Count + cell.Value
    End If
    Next
    SumBold = Count
    End Function

    Assume your range is A1:A20, In A21 enter this formula = SumBold(A1:A20). This should then only sum those cells whose values are in bold font. Be aware however that this will not automatically recalculate by merely changing the font to or from bold, you will have to use the F9 key to force recalculation (or re-enter/change the data)

    Hope this helps
    regards
    Derek



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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