This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below.
ISFORMULA function
TheISFORMULA functionreturns TRUE if a cell contains a formula, and FALSE if not.
Each TRUE value represents a cell thatdoescontain a formula.
The next step is to convert the TRUE and FALSE values to 1s and 0s.
To learn more about this topic, seeWhy SUMPRODUCT?
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.
Related videos
Boolean operations in array formulas
