Excel question for the experts

[Parthol]

Hi there!

Does anyone here know if it's possible to do the following in Excel?

I have a column (or row) of numbers, some of which are formatted in bold.

I want to count the number of cells in that row (or column) that are so formatted. (As opposed to the sum of those numbers).

In other words, I want a formula that returns: "how many cells within this range are formatted in bold?"

Thanks,

-P

[Giles]

I think it depends on what version of Excel you are using. On the computer that I'm on at present is Excel 2003, and I think you can't do that sort of thing. However, I believe that you might be able to do in in Excel 2007: I've been told that you can do tests of cell format with that version.

[Parthol]

I'm using the 2007 version.

[Ruminator]

Originally Posted by Parthol

In other words, I want a formula that returns: "how many cells within this range are formatted in bold?"

I believe you have to use VBA code (Visual Basic Applications) to test whether the cell's properties are bold. You use VBA to write a UDF (User Defined Function) that specifically tests for bold. I'm sure this type of function has be re-invented a 1000 times by different people so a google search should find some example code to paste into your worksheet.

Giles might be thinking of a function in Excell called CELL() but that only returns the formatting string such as "$0.00" or if the cell is protected/unprotected -- the CELL() doesn't provide color, bold, etc.


(Tangent thought... I've always wondered why people ask Excel questions here instead of dedicated forums such as http://www.excelforum.com where there are more experts and better answers.)

[CookingWithGas]

I was unable to find this feature in Excel 2007. Doesn't mean it isn't there.

This function works in Excel 2007.

Function CountBold(r As Range) As Long
   CountBold = 0
   
   Dim c As Range
   For Each c In r
      If c.Font.Bold Then
         CountBold = CountBold + 1
      End If
   Next c
End Function

However, a change to formatting is not considered a change to the cell so will not trigger an automatic recalculation of this function. You could declare it as Volatile, but even that won't guarantee it will be recalculated when formatting changes.

Originally Posted by Ruminator

(Tangent thought... I've always wondered why people ask Excel questions here instead of dedicated forums such as http://www.excelforum.com where there are more experts and better answers.)

Bite your tongue


Actually, people come here because it's handy and quick. That's the tradeoff of a general-purpose board. Big audience who know a little about everything. Specialized boards know everything about a little. I happen to like www.ozgrid.com for Excel help, and I guess there are others that are very good too.

[tomndebb]

Also, many of us talk something closer to English than the denizens of tech centered boards. If you are newly fighting your way into a situation, it helps if your guide speaks a language you understand. There are multi-lengual tech geeks and some of us only speak tech, but the odds of getting an answer in English, here, are mildly higher.

[CookingWithGas]

Originally Posted by tomndebb

Also, many of us talk something closer to English than the denizens of tech centered boards. If you are newly fighting your way into a situation, it helps if your guide speaks a language you understand. There are multi-lengual tech geeks and some of us only speak tech, but the odds of getting an answer in English, here, are mildly higher.

That is true, but there is a reason for that. I have found that over half of the Excel questions I try to answer are so poorly worded that I have to ask a lot of questions to find out what the person is trying to do, and what problem they are having. Sometimes just reading the question shows that it isn't worth it to try to answer it.

Note that Parthol did an excellent job in formulating a precise, brief, clear question!

[Parthol]

Just wanted to pop back in to say 'thanks' for the responses!

Also, yeah, I asked the Dope because it's quick, familiar, and the answers are typically just as good as those from tech forums.

-P