MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding bold values


Posted by Robert on August 16, 2001 4:29 PM

I have a worksheet with bold numbers scattered throughout a column. Is it possible to add only the bold values...I guess visual basic would be involved?


Posted by Aladin Akyurek on August 16, 2001 4:40 PM

Just Curious: What is the condition for those numbers to be in bold?

Posted by Robert on August 16, 2001 4:44 PM

The numbers are column headings...divisional groups.


Posted by Aladin Akyurek on August 16, 2001 5:49 PM


I thought the bold numbers were in a column range, now it appears they are column headings (representing divisional groups), probably in a single row (row 1 perhaps)? Why you would want to total column headings escapes me.

Anyway here a UDF that can tell whether a cell value is bold.

Function ISBOLD(cell) As Boolean
'
' Returns TRUE if cell is bold
' Walkenbach
'
ISBOLD = cell.Range("A1").Font.Bold
End Function

Let assume that you have a series of numbers in A2:A10.

In B2 enter: =ISBOLD(A2)*A2 [ copy down this as far as needed ]

In B1 enter: =SUM(B2:B10)

If some VBA'er around this board can make this UDF return an array of boolean values, the following single formula would be possible:

=SUMPRODUCT((ISBOLD(A2:A10))*(A2:A10))

Aladin

Posted by Ivan F Moala on August 16, 2001 9:43 PM

Try this UDF

Function SumBold(SumRg As Range) As Double
Dim ocell As Range
Dim Tot As Double

Application.Volatile
For Each ocell In SumRg
If ocell.Font.Bold = True Then
Tot = Tot + ocell.Value
End If
Next
SumBold = Tot
End Function

Post if further help required

Ivan Returns TRUE if cell is bold Walkenbach

Posted by Aladin Akyurek on August 16, 2001 10:51 PM

UDFs

Darn it, I get more and more dragged into the VBA thing! Try this UDF Function SumBold(SumRg As Range) As Double

Thanks, Ivan. Filed. : If some VBA'er around this board can make this UDF return an array of boolean values, the following single formula would be possible:

I didn't yet see (admit: didn't try hard enough) a good UDF example returning an array of (boolean) values. Can I get one?

Aladin

Posted by Robert on August 17, 2001 11:07 AM

Re: UDFs

You guys are awesome...thanks!