where "sales" is the named range C5:C13, values in C5:C9 are hardcoded, and values in C10:C13 are generated with formulas.
This formula uses boolean logic to "filter" the numbers in column C before summing with the SUMPRODUCT function. When the formula is evaluated, values in the named range "sales" are expanded into an array:
To filter out any values that aren't created by a formula, the ISFORMULA function is to create an array of TRUE FALSE values like this:
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
The Excel ISFORMULA function returns TRUE when 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.