COUNTIF

[Zachary Turner]

I want to count all entries in a range which are bold. I wrote a
function IsBold() that works if I give it a single cell as an argument.
It either returns TRUE or FALSE. What doesn't work is when I try to
use this function on a range with the COUNTIF. My syntax was
=COUNTIF(A1:A100, IsBold())

Is my sntax wrong? Thanks

 

[KL]

Hi Zachary,

The second argument in the COUNTIF function is looking for a cell value. So
in your case it looks for the cells in the range [A1:A100] which have the
value TRUE and (not suprisingly) doesn't find any. If your function accepts
range argument and works on multiple cells (i.e. returns an array of
TRUE/FALSE values for each cell evaluated) then as an alternative you could
use:

=SUMPRODUCT(--IsBold(A1:A100))

Regards,
KL

 

[Dave Peterson]

My bet is that your =isbold() has to be changed to accept a range larger than
one cell.

Option Explicit
Function IsBold(rng As Range) As Variant

Dim myCell As Range
Dim myArr() As Variant
Dim iCol As Long
Dim iRow As Long

If rng.Cells.Count = 1 Then
IsBold = CBool(rng.Font.Bold = True)
ElseIf rng.Areas.Count > 1 Then
IsBold = CVErr(xlErrRef)
Else
ReDim myArr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
For iCol = 1 To rng.Columns.Count
For iRow = 1 To rng.Rows.Count
myArr(iRow, iCol) _
= CBool(rng.Cells(iRow, iCol).Font.Bold = True)
Next iRow
Next iCol
IsBold = myArr
End If

End Function

And your function needs to be changed to somthing like:
=SUMPRODUCT(--isbold(A1:A10),B1:B10)

 

[Dave Peterson]

Oops. I just KL's response and saw that you were using =countif(). I missed
that in the subject and twice in the text!

This function will work like KL suggests:

=sumproduct(--isbold(a1:a10))