Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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 |
#5
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How can I sum only amounts that are in BOLD format within a column | New Users to Excel | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) | |||
Paste a formated Cell with protection can it be done | Excel Discussion (Misc queries) | |||
How can I change decimal amounts to end with .95 | Excel Worksheet Functions | |||
sumproduct to add total amounts for the month | Excel Worksheet Functions |