JoeU2004 wrote:
"John Gregory" wrote:
The formula returns the correct
information if the range is filled in, but if the range is not filled in
and the cells in that range are empty, I get an error message.
[....]
=SUMPRODUCT((Zone=B8)*(O2scfm0)*(O2scfm*diversity ))

Try:

=SUMPRODUCT((Zone=B8)*(O2scfm0), O2scfm, diversity)

Explanation: SUMPRODUCT ignores cells with text in ranges that are
individual parameters. But if those text cells are encountered in
evaluating an arithmetic operation such as "*", they cause an error.

The form (...)*(...) is useful when to write "co-conditions", i.e.
conditional expressions that both must be true. Not only does the
multiplication (*) "and" the conditionals, but also it converts them to
numbers (0 or 1), which SUMPRODUCT requires.

However, the form expr*expr is not needed for the sum of products of
values, the actual result of SUMPRODUCT. It is sufficient to write
expr,expr -- separate parameters -- since SUMPRODUCT performs the
multiplication.

Note: The cells causing the problem are not "empty". Truly empty cells
(i.e. no value or formula) do not cause problems in arithmetic
expressions. The cells probably contain the null string (""), for
example the result of a formula like =IF(condition, "", expression)

Excellent exposition, explanation and solution, JoeU2004. In early times
I adopted the notation

SUMPRODUCT(--(cond1),--(cond2),...)

but for whatever reason I became "lazy" and started writing

SUMPRODUCT(cond1*cond2*...)

This example gives good reasons why one needs to be careful.

Nice post, Sir.