How can I sum amounts formated as B O L D only?
 

Hi!
I need to get a total from a worksheet that has hundreds of amounts in it.
However, I only need the total of the amounts that were marked with have BOLD
font. Please help me I don't have much experience with EXCEL.

Thank you very much!

W a n d a

Hi, Wanda-
By any chance, is there a word next to the bold number? Maybe the word
"Total"? It would be easier to derive the total using a word than
using the bold format.

On Fri, 11 Mar 2005 11:37:06 -0800, "wanda"
wrote:

Hi!
I need to get a total from a worksheet that has hundreds of amounts in it.
However, I only need the total of the amounts that were marked with have BOLD
font. Please help me I don't have much experience with EXCEL.

Thank you very much!

W a n d a

You will need to use a User Defined function written in VBA in order to do
this. But two caveats:

1. The formula will NOT recalculate if all you do is change the font attribute
of any cells in the target.

2. If the BOLD is due to conditional formatting, this UDF will not work.
However, you would be able to use a worksheet formula using the same condition.

3. As written, if a Value in the range is BOLD but is NOT a number, you will
get a #VALUE! error

To enter this UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then, from the top menu bar of the
VB Editor, select Insert/Module and paste the code below into the window that
opens.

To use the UDF, enter =SUMBOLD(rg) into some cell where rg is the range of
cells that you wish to scan for bold cells and sum them.

========================
Function SumBold(rg As Range) As Double
Application.Volatile
Dim c As Range

For Each c In rg
If c.Font.Bold = True Then
SumBold = SumBold + c.Value
End If
Next c

End Function
==========================



--ron

I expect the second part of the question was answered by now but
rather than going back to the newsgroup, I'll just say to include
a test for being a number. I got sidetracked to try to find out
if the actual cell address of the formula could be determined
(I don't think it can be).

if IsNumeric(c) then SumBold = SumBold + c.Value

On Sat, 12 Mar 2005 09:57:44 -0500, "David McRitchie"
wrote:

I expect the second part of the question was answered by now but
rather than going back to the newsgroup, I'll just say to include
a test for being a number. I got sidetracked to try to find out
if the actual cell address of the formula could be determined
(I don't think it can be).

if IsNumeric(c) then SumBold = SumBold + c.Value


I thought of that, but then decided to let the OP make the call. I think one
could make a case that it's important to know if some cells that are supposed
to be numeric are not (e.g. l instead of 1).

If that's not important, one could also do:


SumBold = SumBold + Val(c.Text)

I'm not sure which would be faster, though.


--ron