When working with an Excel worksheet, you may notice that many cells contain formulas. To figure out the number of cells that are formulas, we will use the ISFORMULA function and the SUMPRODUCT function.
The ISFORMULA function checks whether a cell is a formula or not, and the SUMPRODUCT function returns the product of numbers in an array.
Example: In the following database, the first name is in column A, the second name is in column B, and the results are in column C. For practice purposes, the results in column C include different types of values.
Formula: To get the number of cells that are formulas in column C
=SUMPRODUCT(–ISFORMULA(C2:C12))
In the example, two cells are formulas (C4 and C5). The formula is =C2+C3 in C4, and the formula is =C3+C4 in C5.
Explanation:
- Step 1: The ISFORMULA function returns true if the cell is a formula and false if not;
- Step 2: The double hyphens are to convert true to one and false to zero;
- Step 31: The SUMPRODUCT function returns the sum of the products of the new array.
Notes: The SUMPRODUCT Function
The SUMPRODUCT function adds all the multiplication results for all arrays.
Formula:
=SUMPRODUCT(array1, [array2], …)
Explanations:
– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.