In this example, the goal is to calculate a sum of the values in a range that are generated with a formula. In other words, we want to sum values in a range while ignoring the values that have been entered manually. In the context of this example, the hardcoded values in C5:C12 represent actual sales values and the values in the range C13:C16 represent forecasted values. This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below.
The forecasted values in the range C13:C16 are created with a formula based on the MROUND function. The formula in C13, copied down, is:
This formula is used to generate values that are 5% higher than the previous month, rounded to the nearest multiple of 25.
To sum values in the range C5:C16 that are created with formulas, the formula in F6 is:
ISFORMULA returns TRUE when cell contains a formula, and FALSE if not. In this case, there are 12 values in the range C5:C16, so ISFORMULA returns 12 results in an array like this:
Each TRUE value in this array represents a cell that contains a formula. Notice the last 4 values are TRUE. When this array is multiplied by the named range sales (C5:C16), the math operation coerces the TRUE and FALSE values to 1s and 0s. We can visualize the formula at this point like this:
After the multiplication takes place, we have a single array like this:
Now you can see how the filter works. The values not created by formulas are "zeroed out". With just one array to process, SUMPRODUCT sums the array and returns a final result of 5950.
To sum values not generated by a formula, you can add the NOT function like this:
This is the formula in cell F7. Here, the NOT function reverses the TRUE FALSE results returned by ISFORMULA function:
This causes the formula-created values to be zeroed out:
The final result from this formula is 9243.