Countif Bold Formula

[Rockhopper3]

Does anyone know if there is a formula that uses the countif function (or something like it) that will count cells in a range only if they are in bold text?

 

[meldoc]

Hello

Try this UDF.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> CountBold(test <SPAN style="color:#00007F">As</SPAN> Range)<br>  Application.Volatile<br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> a <SPAN style="color:#00007F">In</SPAN> test<br>    a = a.Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> a <SPAN style="color:#00007F">Then</SPAN> CountBold = CountBold + 1<br>  <SPAN style="color:#00007F">Next</SPAN> a<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

 

[Rockhopper3]

Meldoc,

Thanks for the reply. I'm trying to avoid using macros if possible. Do you know of a formula you can use in the actual workbook and not in VBA?

 

[Rockhopper3]

Using the above VBA code, how can I set it up so that it scans column B for items in Bold and then displays how many items are in bold in Cell O3?

 

[Peter_SSs]

Using the above VBA code, how can I set it up so that it scans column B for items in Bold and then displays how many items are in bold in Cell O3?

I would use slightly different code (see below) but to set up & use the UDF ..

1. With the relevant workbook active, Alt+F11 to open the VBA window.

2. Use the menu to Insert|Module

3. Copy and paste the code into the main right hand pane that opes at step 2.

4. Close the VBA window and enter a formula like this in O3:
=CountBold(B1:B100)

Notes:

1. You could use a whole column, =CountBold(B:B), but this will slow your sheet considerably since the code would need to check every cell in column B so best to restrict the range if you can.

2. This will count cells that are formatted as bold, even if they don't contain any value. If this doesn't suit then a firther check would need to be built in to the code.

3. If you subsequently (after the formula has been entered in O3) add/remove bolding from any of the target range, the UDF formula will not automatically update. It will only update when the something on the sheet is re-calculated.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> CountBold(R <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>  <br>  Application.Volatile<br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> R<br>    <SPAN style="color:#00007F">If</SPAN> c.Font.Bold <SPAN style="color:#00007F">Then</SPAN> CountBold = CountBold + 1<br>  <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

 

[Peter_SSs]

There is another method without doing anything in the VBA window but it does technically still use macros and also requires a helper column. If you want to investigate that method, post back and include what Excel version you are using.

 

[Rockhopper3]

Peter,

Thank you for your quick reply. I would like to see this other method that you are talking about. Currently I am using Excel 2007.

 

[Peter_SSs]

Try this. It assumes Column C is available as a helper column.

1. Formulas ribbon tab|Define Name|Name: FBold|Refers to: =GET.CELL(20,INDIRECT("RC[-1]",FALSE)) |OK

2. Enter the formula shown in C1 and copy down as far as you might need. (Column C could then be hidden if you want)

3. Formula in O3 as shown.

Excel Workbook
	B	C	D	N	O
1	xxxxx	0
2	xxxxx	1
3	xxxxx	1			5
4	xxxxx	0
5	xxxxx	1
6	xxxxx	0
7	xxxxx	0
8	xxxxx	1
9	xxxxx	0
10		0
11		1
12		0
Count Bold

#VALUE!

</td></tr></table></td></tr></table>



Note that, as I mentioned earlier, C11 in my sheet shows a '1' because B11 is formatted as bold even though it doesn't contain anything. If that is not what you want, try changing the C1 formula to:

=IF(B1="",0,FBold+0)

 

[T. Valko]

Peter,

Thank you for your quick reply. I would like to see this other method that you are talking about. Currently I am using Excel 2007.

For what it's worth...

Like the UDFs, the non-VBA method "suffers" from the same characteristic in that changing the cell format does not trigger a calculation.

In my humble opinion you should not base calculations on cell formats.

Try to write a formula based on the logic of WHY the cells are formatted as bold. If that's not possible then use a helper column to "mark" which cells should be counted.

 

[T. Valko]

P.S.

Also note, if you're using Excel 2007 or later and using the non-VBA method then you have to save the file as a macro enabled file in the *.xlsm format.