Sum total bolded figures in a column

I am trying to come up with a simple cell formula to total or sum
amounts bolded in a column.

Any Ideas?

Thanks from a person needing help.

mikeburg


-- 
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
View this thread: http://www.excelforum.com/showthread.php?threadid=392229

0
8/2/2005 4:19:26 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
63 Views

Similar Articles

[PageSpeed] 32

There's nothing built into excel that will sum values based on formatting.

You could use a User defined function, though.

Option Explicit
Function SumBold(rng As Range)

    Application.Volatile

    Dim myCell As Range
    Dim myTotal As Double
    
    myTotal = 0
    For Each myCell In rng.Cells
        With myCell
            If .Font.Bold = True Then
                If IsNumeric(.Value) Then
                    myTotal = myTotal + .Value
                End If
            End If
        End With
    Next myCell
    
    SumBold = myTotal
    
End Function

Be aware that changing formats won't cause excel to recalculate.  You could be
one calculation cycle behind.  I'd hit the calculate key (F9) before I trusted
the value in the cell.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbold(a1:a10)

You didn't ask, but Chip Pearson has similar routines based on colors.  You may
want to look at that, too:

http://www.cpearson.com/excel/colors.htm



mikeburg wrote:
> 
> I am trying to come up with a simple cell formula to total or sum
> amounts bolded in a column.
> 
> Any Ideas?
> 
> Thanks from a person needing help.
> 
> mikeburg
> 
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
> View this thread: http://www.excelforum.com/showthread.php?threadid=392229

-- 

Dave Peterson
0
petersod (12004)
8/2/2005 7:58:57 PM
Reply:

Similar Artilces:

How do I convert some data in the row to columns?
I have an issue that I need to resolve. I'm currently getting data in a row format, but I need to convert some of the data into columns, for importing into Access. For example...... Item Description Jan 05 Feb 05 Mar 05 Apr 05 ........... ABC123 Widget A 100 250 175 210 ............ XYZ123 Widget B 50 75 100 76 ............ To convert to........ Column1 Column2 Column3 Column4 Item Description Date Qty ABC123 Widget A Jan 05 100 ABC123 Widget A ...

format columns to collapse and expand in excel
I already did this in an old excel worksheet but I CAN'T remember HOW I DID IT. The result is a (plus) + and a (minus) - sign at the top of the worksheet in row 1 at the location where you want your selected columns to either expand or collapse. I need to once again implement this handy formatting trick but for the life of me remember how I did it....so frustrating! Anyone out ther know how to do this? Move your cursor to the top of the column you wish to AutoFormat.........say it's column C, then ease the cursot to the right to the separation between Column C and column D and ...

Matching the colors Column Charts and Pie Charts
I have a file that contains three graphs. Two pie chats, one charting revenues and the other charting expenses, showing the contribution from each division of a department. At the top of the page i have a column chart graphing revenues and expenses together. The source data for all three charts is coming from the same data. I am trying to find a way to create a column chart that will use a different color for each division, like the pie chart does, instead of one color for revenues and one for expenses. Is this Possible? -- RohanSewgobind --------------------------------------------...

Column Alignment Problem
My "message" column disappeared, and when I added it back into the view, the text is aligned to the right and will not adjust back - even tho I reset the alignment to the left! Now it will not sort the "nmessage" column and half of the text of messages names is out of view. Help?! Thanks Try resetting the view -- View | Current View | Define Views, select the view that's messed up, and click Reset. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread....

Combining IF and COUNTIF based on two columns
Here is what I'm trying to do, I have two colums with data, the first column will have one of four letters (A, B, C, D) the second column will have one of two numbers (0, 1). I need to get 6 different counts: COUNTIF column A range A1:A250 is A COUNTIF column A is range A1:A250 B COUNTIF column A is range A1:A250 C COUNTIF column A is range A1:A250 D COUNT column A ONLY IF column B range B1:B250 is 0 COUNT column A ONLY IF cloumn B range B1:B250 is 1 I can do the first 4 calculations pretty easy but am having a hard time figuring out how to do the last two calculations. Can anyone h...

Text box bound to non-default combo box column
I have a text box that I would like bound to a drop down control. It is easy enough to set the Control Source to equal the drop down's default column value (i.e. "=[ComboBoxName]"), but the combo box has three columns, and I want it bound to a non-default column. I have tried "=[ComboBoxName](2)" and "=[ComboBoxName(2)] where (2) is the column that I want to bind to with no luck. Any info is greatly appreciated! jtertin wrote: > I have a text box that I would like bound to a drop down control. It > is easy enough to set the Control Source to equal the d...

Comparing dates in columns from linked table
Hi, I'm new to Access/SQL and have a problem with a query that I just cant seem to be able to solve. So, I have an Access database containing client number, security name and date, that information has to be updated every month, the source for the update being an excel file that I link to the Access database. But I dont want to overwrite on the existing data so what i wanted to do was add the current month's data, when it's not already in there. What I did was an insert query which inserts the data from the excel file to the table in the Access database only if the c...

Hiding Columns with Zero Data
Is is possible when creating a chart to ignore columns that have no data in them. Such as a Data Table with 31 Days but not all days have data against them. Ta Hi, Maybe Jon Peltier's squeezed columns example can help. http://peltiertech.com/Excel/Charts/SqueezeColumns.html Cheers Andy Goodwin3338 wrote: > Is is possible when creating a chart to ignore columns that have no data in > them. Such as a Data Table with 31 Days but not all days have data against > them. Ta -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Sum total hours worked in excel
I want to add the total hours worked in excel that I book for the month for example: A B C Start Time End Time Total 08:00 16:00 08:00 08:00 14:30 06:30 08:00 15:00 09:30 Total 00:45 It stop at 23:59 and I want it to go on like a total sum, wat formula must I use to do my total sum calc for my time? please help Frankie e-mail: frankiedeconing@harmony.co.za You should format the cells in column C as [h]:mm - that way they will not wrap round above 24 hours, but ...

Making Text Fit Flush In Columns
Hi everyone, is there a simple method to ensure that the text in my 9.75 H x 3.66 W text boxes fits flush top to bottom so each column of text has perfect alignment? My master page creates my margins but I am then inserting text boxes into these columns. (is this okay or maybe what is causing the misalignment? Thanks in advance. What version Publisher are you using? Have you tried using the Baseline guides? View, Baseline guides. The text size is formatted in the Arrange menu. Format, paragraph, check align text to baseline guides. You will have to do this for every text box. Why are...

Total in Footer
Hello, I'm new to access and have a question regarding the footer. I created a text box and inserted a basic equation for the the total cost of the order, =Sum( [UnitPrice]*[Quantity]), however it is not appearing on the form. The visibility property on the footer is set to Yes. Please let me know what I am missing - Thanks - On Mon, 26 Apr 2010 19:58:53 GMT, "TheLee" <u59624@uwe> wrote: >Hello, >I'm new to access and have a question regarding the footer. I created a text >box and inserted a basic equation for the the total cost of the order...

change color of columns depending on value
Hi use 'Format - Conditional Format' for this. See: http://www.contextures.com/xlCondFormat01.html -- Regards Frank Kabel Frankfurt, Germany "van Velzen" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:3e9301c4ac69$44f2e890$a501280a@phx.gbl... > And just in case you meant chart columns have a look at http://peltiertech.com/Excel/Charts/ConditionalChart1.html Frank's so prolific he may have missed the NG you posted in <vbg> Cheers Andy Frank Kabel wrote: > Hi > use 'Format - Conditional Format' for this. See: > ht...

how to convert multiple columns of data into one single column?
as subject, e.g. from... A B C D Mary Peter Paul David John Nancy Lucy Ken Alice Annie Danny Jane to... A Mary John Alice Peter Nancy Annie Paul Lucy Danny David Ken Jane Many thanks! Hi try the following formula on the second sheet in cell A1 =OFFSET('sheet1'!$A$1,MOD(ROW()-1,3,INT((ROW()-1)/3)) and copy down -- Regards Frank Kabel Frankfurt, Germany "���T" <,> schrieb im Newsbeitrag news:#6hXAJMyEHA.1404@TK2MSFTNGP11.phx.gbl... > as subject, e.g. > > from... > > A B C D...

Rows to columns
Dear friends, Happy New Year to all of you. Here is my big problem: I have developed a relational database using access 2003 with the following structure: -T_Polygons (PolygonID – text 6 – Primary key) -T_Data1 (PolygonID and SpeciesID – Primary keys) -T_Data2 (PolygonID and SpeciesID – Primary keys) -T_Data3 (PolygonID and SpeciesID – Primary keys) As you realize, each Polygon has several records to the other related tables. I want to run a query (perhaps) and give me as column A the polygon ID and the following columns (0-38 for each related table, or just the 3 first records) to be t...

Dates posted within a standard column chart
I have a regular column chart with dates at the bottom, and the columns are the # of cases made per week. I'm trying to include the dates the Case specifications were changed within the chart. I only want to have a point within the chart showing the date it was created within the dates already showing. For example, I have the dates of 10/1, 10/6, 10/13, and 10/20 on the X-axis. There are four columns per date. The dates of the specification changes are 10/9, 10/14, and 10/15. I'd like points showing on those dates with Version 1, Version 2, and Version 3 marked. Any ideas...

Merging columns? #2
::Thanks for responding, Myrna. There is only one row per person-- and no, there is not a blank ro between each filled row. Carol : -- Carolina7870 ----------------------------------------------------------------------- Carolina78703's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1562 View this thread: http://www.excelforum.com/showthread.php?threadid=27225 If you have only one row for each person, I guess I don't understand what you want to do. Combine the entries in A1, B1, C1, and D1, with a space between? If so =A1&" "&B1&&...

Excel Column Headings Compatible with Outlook to import.
When I try to import an excel file into Outlook - I get the message to go back into excel and name the ranges. I understand highlighting the area - going to insert/name/define/add/ok. Here's where I'm having difficulty. I receive a contact file with names and phone numbers. At the top of column A it says Name and below in the same column is the list of names. I right click on the first cell in the column and choose "pick from list" - then a drop down window apprears with a list of the names in the column. When I go through the same process with column B which has...

look up and match exact data is adjacent column
I am trying to import notes from a current data base program, which I exported into excel, into a new database program which requires me to match a unique case senstive ID (alpha & numeric) to an list of corresponding account numbers from the existing database. To be clear, both databases contain the same list of account numbers, but only the new database has the required unique case senstive ID, which is why I need to match it with the old database info. I had partial success using the Lookup function, but it would not provide an exact match. A sample of my work can be seen below....

Column Headings #8
I have received a spreadsheet where the column headings are numbers and the row headings are also numbers. Can the column headings be changed back to letters? If so, how can I do this? There are instructions for changing this in the Excel FAQs here: http://www.contextures.com/xlfaqApp.html#HeaderNumber '========================= Choose Tools > Options and select the General tab. Remove the check mark from 'R1C1 reference style' '============================= Brian wrote: > I have received a spreadsheet where the column headings are numbers and the row headings a...

How to create a stacked, staggered column graph w/ 2 y axes
Hi everyone. I searched the forum and couldn't find any answers to this. I'm trying to compare two years worth of data using the following information (w/ Excel 2003): Stacked: A vs B Staggered: 2006 vs 2007 2 Axes: % of A to B 2006 vs % of A to B 2007 Anybody have a clue on how to do this? I can create a stacked graph of 2006-2007 data w/ a y axes for % of A to B; but it would make much more sense if i could compare the years side by side rather than linearly to look for seasonal trends. any ideas? daobrien: What about using day of year trend chart to compare 2006 versus 2007? H...

Subtable that automatically expands with totals on the top
I would like to create a little worksheet to do some calculations on some sample data. I would like a section of the worksheet to act like a "subtable" with one row for each set of sample data, headings across the top, the ability to add new rows at the top, and with the totals also at the top. For example, if the subtable starts like this: n Head1 Head2 Head3 ... Tot total1 total2 total3 ... 1 data11 data12 data13 ... I would then like some way (ideally a function key or keyboard shortcut) to add a row BEFORE row 1 so that it looks like this: n Head1...

Combo Box
Hi, AFTER a user has choosen a row from a combo box, it appears that the combo box automatically display the first printable column. Is there a way to display someother column than the 1st printable column? Sometime I would like to print the 2nd or 3rd column. Is there a way to do this? -- Dennis Dennis wrote: > Hi, > > AFTER a user has choosen a row from a combo box, it appears that the > combo box automatically display the first printable column. > > Is there a way to display someother column than the 1st printable > column? Sometime I would...

To find out the total number of hrs worked in a week
Hai all I have two column in an excel sheet A1 and B1 A1 cell contain total number of hrs in a day worked ie 6:30 (hh:mm) format B1 cell contain number of days ie, 5 Please how can find out the total working hrs ie 6:30*5 ie total hrs worked is 32.30 hrs . Please give me a solution to arrive this answer With thanks Pol =A1*B1 then (IMPORTANT) custom format the cell as [h]:mm If you need to calculate a total amount using an hourly rate use =((A1*B1)*24)*rate and format as currency or number -- Regards, Peo Sjoblom "pol" <pol@discussions.microsoft.com>...

sum function with range dependant on date
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C6ACBE.D8B879D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the following data 1/1/06 50 2/1/06 40 3/1/06 30 4/1/06 20 In January, I want to sum all four rows (50 through 20). In February, I = only want to sum the last three rows (40 through 20). In March, 30 = through 20 and in April 20 through 20. I want to tell the sum function = which month to start by referring to a cell with the month (i.e. 2/1/06 = for the 40 through 20 sum). I cur...

Macro to change Chart Range when inserting a column
I have created a workbook to use as a weekly reporting tool for different sites across the country. It contains a "Report" sheet and a "Data" sheet. There are 4 charts embedded into the Report sheet. I have set-up the Data sheet to have the data titles in Column A, a 12-week summary in Column B, and the weekly data begins at Column C. For the 12-week, I used the formulas =sum($C$1:$P$1) and on the Chart series, I used =Data!$C$1:$P$1. I designed the sheet so a center would insert a column at Column C each week for the most recent data. Unfortunately, everytime th...