CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
mwaks
mwaks

Reputation: 389

Counting the number of unique formulas in an excel sheet

I have a big workbook with 50+ worksheets and I would like to count the number of unique formulas in the workbook. I used the inbuilt to find option to find '=' but this returns duplicate formulas due the drag option in excel. Any ideas would be appreciated. Thanks.

Upvotes: 0

Views: 179

Answers (2)

Kvnc
Kvnc

Reputation: 1

You can also find out how many formulas are on a page by typing the formula below:

=SUMPRODUCT(IFFORMULA(A:M)*1)

But remember to subtract 1 from the number if the cell in which you type the formula is within the specified range “A:M”

Upvotes: 0

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

This is a sliiightly different answer than you may be expecting, but if you go to the FORMULAS ribbon, -->Formula Auditing section --->Show Formulas, it will change all cells so that they display what's actually in the formula bar, instead of what the simplification of that formula is.

Using this is often the simplest way to check for what formulas are actually present in a workbook.

As for counting the 'unique' formulas you are using, that's possible, but could you clarify why you want it? What will you be using that information for? If you explain your needs a little more clearly someone could likely provide a more useful solution.

Upvotes: 0

Related Questions