Condition on chracter or cell format

bam2000

New Member
Joined
Apr 12, 2002
Messages
2
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top