SUM cells that contain formulas

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 as 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.

How to SUM cells that contain formulas

We aim to sum calculated cells in the D3:D11 range and skip the hardcoded 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)
ISFORMULA function creates an array and splits the result into TRUE and FALSE results

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.

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.