Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF
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 |
#2
|
|||
|
|||
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 "Zachary Turner" wrote in message ups.com... 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 |
#3
|
|||
|
|||
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) Zachary Turner wrote: 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 -- Dave Peterson |
#4
|
|||
|
|||
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)) Dave Peterson wrote: 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) Zachary Turner wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |