Formula/Function for counting formulas in a range

[Henk Stander]

Formula or Function for counting formulas in a range for Excel 2003 - 2010:

Is it possible to count formulas in a range by making use of a formula or function for Excel versions 2003 - 2010?

Regards,
Henk Stander

[karedog]

Hi,

Try this UDF :

Please Login or Register  to view this content.

Regards

[Henk Stander]

Thanks karedog,
It is working perfectly.

I wonder it it would be possible without an UDF as well.

Regards,
Henk

[karedog]

You are welcome Henk.

I think it will be very difficult by using regular formulas. For example, if we try to detect this by checking Left(cell,1) = "=", it is possible that the cell is formatted as text and the cell's content is "=blabla" but this cell is not formula (it is a string), so this method won't work.

Regards

[Henk Stander]

@Karedog
It is possible to use =Cell("type",A1) to distinguish between a formula and text, "l" for " '=3*A3 " and "v" for " =3*A3 ". The problem is that "left" is looking at the result of the formula, i.e. the cell value and not the real content.

Regards
Henk

[XOR LX]

It is possible to use =Cell("type",A1) to distinguish between a formula and text, "l" for " '=3*A3 " and "v" for " =3*A3 ".

But that function returning "v" does not in any way tell you that that cell contains a formula: it simply gives you information about the datatype of the value returned by that formula.

In 2013 there is a FORMULATEXT function which might go some way towards offering a non-VBA solution, though beyond that and using e.g. GET.CELL (which is technically a macro anyway, even though it can be called via Name Manager alone) there is no real solution other than with VBA to your query.

Regards

[Henk Stander]

@XOR LX
I know, that was to address karedog's concern for differentiating between a formula and text starting with an "=" sign. Unfortunately, as you said, it doesn't tell you whether it is a formula or not.

Pitty.

Thanks anyway.

In 2013 the following formula will work:
{=SUM(N(ISFORMULA(A:A)))}
OR:
=SUM(--(ISFORMULA(A1:A20)))

Regards,
Henk

[XOR LX]

In 2013 the following formula will work:
{=SUM(N(ISFORMULA(A:A)))}
OR:
=SUM(--(ISFORMULA(A1:A20)))

Indeed, though I certainly wouldn't recommend the first of those (an array formula processed over an entire column's worth of cells is not a good idea!).

I don't have 2013 yet, though can I infer from these formulas that SUMPRODUCT is not capable of inducing ISFORMULA to operate over an array of values, and so we must use a CSE construction?

Regards

[Henk Stander]

I believe =SUMPRODUCT(--ISFORMULA(A:A)) should also work.

Regards

[XOR LX]

I believe =SUMPRODUCT(--ISFORMULA(A:A)) should also work.

Thanks. Though, please, heed my advice about not using entire column references in such constructions! (Unless this is something that has also been addressed in 2013?)

Regards

[karedog]

I find an article here : http://www.mrexcel.com/forum/excel-q...arguments.html
that using Get.cell function (an old xl 4 macro function), but it must be used using a named range object (cannot directly put as cell's function).

Take a look at my sample file.

Regards

[XOR LX]

I find an article here : http://www.mrexcel.com/forum/excel-q...arguments.html
that using Get.cell function (an old xl 4 macro function), but it must be used using a named range object (cannot directly put as cell's function).

Yes. That's precisely what I said here:

...using e.g. GET.CELL (which is technically a macro anyway, even though it can be called via Name Manager alone)...

and so your workbook would need to be macro-enabled (which you don't appear to have done?).

Regards

[karedog]

Yes. That's precisely what I said here:

You are absolutely right, I just pointed to OP a complete reliable reference with a working example, I hope you don't mind.

Please Login or Register  to view this content.

Isn't it an Excel 2003 XLS file ?

Regards

[XOR LX]

Isn't it an Excel 2003 XLS file ?

Sincere apologies. Need to open my eyes.

Regards

[karedog]

Sincere apologies. Need to open my eyes.

Regards

No problem at all, we live in a fast world

Regards