This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below.
Notice the last 4 values are TRUE.
The valuesnotcreated by formulas are “zeroed out”.
With just one array to process, SUMPRODUCT sums the array and returns a final result of 5950.
Related functions
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
When a cell contains a formula ISFORMULA will return TRUE regardless of the formula’s output or error conditions.
NOT Function
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.
