Using COUNTIF

[Jack]

I have a range of number between 1 and 5. Four of the cells in the range
display formulas. The formulas I am using to determine the number of 1's,
2's, 3's, etc are: COUNTIF(A1:A233,"1");..... COUNTIF(A1:A233,"5"). How do
I get the COUNTIF formulas to count the cells containing the formulas?

 

[Teethless mama]

=SUMPRODUCT(--(A1:A233>=1),--(A1:A233<=5))
or
=SUM(COUNTIF(A1:A233,{1,2,3,4,5}))

 

[Bob Phillips]

You need a UDF to return an array of TRUE/FALSE for formulae

Function IsFormula(rng As Range)
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryFormulae As Variant

If rng.Areas.Count > 1 Then
IsFormula = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then

ReDim aryFormulae(1 To 1)
aryFormulae(1) = rng.HasFormula
Else

aryFormulae = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1
aryFormulae(i, j) = cell.HasFormula
Next cell
Next row
End If

IsFormula = aryFormulae
End Function

and then count them like so

=SUMPRODUCT(--(IsFormula(A1:A10)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)