This tutorial will explain how to sum cells that contain formulas in Excel using the SUMPRODUCT and ISFORMULA functions.
Generic formula to sum results where the cell contains formulas only:
=SUMPRODUCT(range*ISFORMULA(range))How to SUM cells that contain formulas
In the example, we have a sales table that contains orders and sales for a given location. Some of them are a result of a formula (calculated cells). Furthermore, there are locations without exact values, only sales predictions (Rome, Amsterdam, and Sydney)
Here is the solution in a nutshell:
The ISFORMULA has two possible outputs: TRUE or FALSE. We can filter the output based on boolean logic. To do that, place the function inside the SUMPRODUCT formula. Finally, SUMPRODUCT will sum only the TRUE values (where the cell contains a formula).
=ISFORMULA(Sales)As usual, evaluate the formula from the inside out. For example, the picture below shows that the ISFORMULA function creates an array and splits the result into TRUE and FALSE results.
We aim to sum calculated cells in the D3:D11 range and skip the hard-coded values.
First, we will use the ISFORMULA function to decide whether the cell contains a formula or not. After that, the SUMPRODUCT function will help to summarize the matching records.
Multiply two arrays
Now we have two arrays; the first comes from the sales range. The other array is the result of ISFORMULA. If you multiply the two arrays, we’ll get 1’s and 0’s instead of TRUE or FALSE.
Evaluate the following expression:
=sales*ISFORMULA(sales)
When we multiply two arrays, we will get the following result:
{12870, 12375, 0, 0, 0, 14751}Now, apply the array as an argument of the SUMPRODUCT function:
=SUMPRODUCT({12870, 12375, 0, 0, 0, 14751})So, the formula skips zeros and sum cells that contain formulas only; the result is $ 39,996.
Note: This article is a part of our definitive guide: 101 Excel Formulas.
