where sales is the named range C5:C16. The result is $5950, the sum of the values in the range C13:C16, which are created with a formula.
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:
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:
The Excel ISFORMULA function returns TRUE if a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula's output or error conditions.
The Excel NOT function returns the opposite of a given logical or Boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.