Countif with formulae

[Tigerexcel]

With a range of cells how do you get COUNTIF to return the number of cells that contain formulae. I have tried Countif(a1:d5,isformula(a1:d5)) as well as a few other versions but no luck.

 

[Tetra201]

Try

=SUMPRODUCT(--ISFORMULA(A1:D5))

 

[Tigerexcel]

Thanks Tetra, can it be done with COUNTIF or is there a limitation with COUNTIF?

 

[Tetra201]

You are welcome.

IMO, no, this cannot be done with COUNTIF because it compares cell value with the criterion.

 

[Tigerexcel]

Thanks for the explanation, closest I can get is COUNT(IF(ISFORMULA(a1:D5),1,""))

 

[hrayani]

In case you are interested, here is a small UDF

Function count_formulae(rg As Range)
    For Each c In rg.Cells
        If c.HasFormula Then count_formulae = count_formulae + 1
    Next c
End Function

Enter this formula

=count_formulae(A1:D5)

 

[hrayani]

one more option... Non VBA

(hit CTRL+SHIFT+ENTER) after typing the below formula:

=SUM(IF(ISFORMULA(A1:D5)=TRUE,1,0))

 

[Tigerexcel]

Thanks Hrayani. This is what makes Excel my favourite program, the ability to solve a problem in different ways. Like the UDF when it's nice and simple.

 

[hrayani]

Thanks Hrayani. This is what makes Excel my favourite program, the ability to solve a problem in different ways. Like the UDF when it's nice and simple.

You are always welcome....