Condition on chracter or cell format

[bam2000]

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

[Aladin Akyurek]

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?

[Derek]

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

[bam2000]

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

[Derek]

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