This idea isn’t going to work as-is.
A much simpler, cleaner approach is to use an array formula based on the SUMPRODUCT function.
Solution with SUMPRODUCT
TheSUMPRODUCT functionmultiplies ranges or arrays together and returns the sum of products.
It works nicely in this case because it can handle arrays natively in any version of Excel.
Working from the inside out, the expression B5=things returns five values, as explained above.
SUMPRODUCT is designed to process numeric values, and it will simply ignore TRUE and FALSE values.
Now you might see where we are headed.
Since the array contains only numbers, SUMPRODUCT will return a sum.
