Mudit asks, is there any way to sum just the bold cells in a range? This problem is hard to solve in Excel, but easy if you use a tiny user defined function in VBA. In Episode 490, learn how to find free user defined functions on the internet and paste them into your Excel workbook.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel podcast. I'm Bill Jelen.
Today we have a question sent in by Murry from California.
Murry asked is there any way to sum just the bold cells?
You know this is a classic problem that we've had for a long time and I try in the podcast not to go into VBA.
But in this case the solution involves just a tiny bit of VBA.
I actually went out to Google and I searched for "SumBold" that's one word space UDF. UDF is a user-defined function and there are many responses.
There are several of them from the old MrExcel message board.
I'm here I have one from August 2001 by our friend, Ivan Malala.
I'm giving the function for "SumBold".
Now what I'm going to do is, I'm going to actually copy these characters right from the webpage.
Ctrl C.
I'm gonna come back to excel, now if you've never gotten into the VBA before, it's very easy.
We're gonna go to "Tools" "Macro" "Security" Make sure that you are at "Medium".
By default you are at "High".
So, we'll choose medium there and then it's Alt F11 will take you to the VBA editor.
To edit your own function you want to use "Insert" "Module" and I'm just gonna paste Ivan's function "SumBold" right there, and now we're good.
Now we can return to microsoft excel "File" "Close and return to Microsoft Excel" and I can now use this new function the SumBold function =SUMBOLD and specify an area And you'll see that it's totaled up just the bold cells with 586.
Now one interesting thing with a user-defined function is that it only gets calculated, when some numeric change happens in the spreadsheet.
Using bold or turning off bold is not a numeric change.
So if I would actually bold a few more cells, Ctrl B Ctrl B and unbold this cell in order to get the total to update, I have to hit the recalculate button.
That's F9 or if I just naturally enter a number somewhere else in the spreadsheet, the spreadsheet will recalculate.
So that's kind of a caution to watch out for, as you bold and unbold cells that user-defined function will not automatically update.
Again, I hate to have to go to VBA on this one, but unfortunately that's the way that you have to do it.
Luckily there's plenty of examples out on the Internet including at MrExcel.com, of these types of function that you can just copy right in your own version of VBA.
One more. Notice this SomeBold function because I inserted it in the current workbook it's only available in this workbook.
If I wanted it available on all of my workbooks, I would instead store it in a personal macro workbook.
That's the story for another day though.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Murry from California.
Murry asked is there any way to sum just the bold cells?
You know this is a classic problem that we've had for a long time and I try in the podcast not to go into VBA.
But in this case the solution involves just a tiny bit of VBA.
I actually went out to Google and I searched for "SumBold" that's one word space UDF. UDF is a user-defined function and there are many responses.
There are several of them from the old MrExcel message board.
I'm here I have one from August 2001 by our friend, Ivan Malala.
I'm giving the function for "SumBold".
Now what I'm going to do is, I'm going to actually copy these characters right from the webpage.
Ctrl C.
I'm gonna come back to excel, now if you've never gotten into the VBA before, it's very easy.
We're gonna go to "Tools" "Macro" "Security" Make sure that you are at "Medium".
By default you are at "High".
So, we'll choose medium there and then it's Alt F11 will take you to the VBA editor.
To edit your own function you want to use "Insert" "Module" and I'm just gonna paste Ivan's function "SumBold" right there, and now we're good.
Now we can return to microsoft excel "File" "Close and return to Microsoft Excel" and I can now use this new function the SumBold function =SUMBOLD and specify an area And you'll see that it's totaled up just the bold cells with 586.
Now one interesting thing with a user-defined function is that it only gets calculated, when some numeric change happens in the spreadsheet.
Using bold or turning off bold is not a numeric change.
So if I would actually bold a few more cells, Ctrl B Ctrl B and unbold this cell in order to get the total to update, I have to hit the recalculate button.
That's F9 or if I just naturally enter a number somewhere else in the spreadsheet, the spreadsheet will recalculate.
So that's kind of a caution to watch out for, as you bold and unbold cells that user-defined function will not automatically update.
Again, I hate to have to go to VBA on this one, but unfortunately that's the way that you have to do it.
Luckily there's plenty of examples out on the Internet including at MrExcel.com, of these types of function that you can just copy right in your own version of VBA.
One more. Notice this SomeBold function because I inserted it in the current workbook it's only available in this workbook.
If I wanted it available on all of my workbooks, I would instead store it in a personal macro workbook.
That's the story for another day though.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
