Sumproduct error

[John Gregory]

I have a problem with my sumproduct formula. 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. How do I get it to
return the correct amount even if all the cells in the range are not filled
in?

Here is my formula I am using:

=SUMPRODUCT((Zone=B8)*(O2scfm>0)*(O2scfm*diversity))

Zone is a range of cells from $C$5:$C161 it seems to happen in each of the
ranges I have. even if I change the ranges to actual $C$5......etc then it
stills gives error.

any ideas on how to return a correct number?

 

[JoeU2004]

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)*(O2scfm>0)*(O2scfm*diversity))

Try:

=SUMPRODUCT((Zone=B8)*(O2scfm>0), 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)


----- original message -----

 

[John Gregory]

that seems to work - thanks!!

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)*(O2scfm>0)*(O2scfm*diversity))

Try:

=SUMPRODUCT((Zone=B8)*(O2scfm>0), 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)


----- original message -----

I have a problem with my sumproduct formula. 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. How do I get it
to
return the correct amount even if all the cells in the range are not
filled
in?

Here is my formula I am using:

=SUMPRODUCT((Zone=B8)*(O2scfm>0)*(O2scfm*diversity))

Zone is a range of cells from $C$5:$C161 it seems to happen in each of the
ranges I have. even if I change the ranges to actual $C$5......etc then it
stills gives error.

any ideas on how to return a correct number?

 

[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.

I think you misunderstood my posting. This is nothing lazy, not wrong with
SUMPRODUCT((cond1)*(cond2)*...).

The purpose of writing --(cond1) is to convert TRUE and FALSE to 1 and 0,
which SUMPRODUCT requires.

But any arithmetic operation has the same effect.

SUMPRODUCT(--(cond1),--(cond2),...) is effectively the same as
SUMPRODUCT((--(cond1))*(--(cond2)),...). SUMPRODUCT((cond1)*(cond2),...) is
just a simplication of that.

The issue I was talking about is not with the conditional expressions, but
with the arithmetic expressions.

For example, for SUMPRODUCT((A1:A10>0)*(B1:B10<0), C1:C10 * D110), the
potential problem is with C1:C10 * D110.

If any of C1:C10 or D110 can be text, that form will results in a #VALUE
error.

It is more reliable to write SUMPRODUCT((A1:A10>0)*(B1:B10<0), C1:C10,
D110) because SUMPRODUCT "treats array entries that are not numeric as if
they were zeros" (from the help page).


----- original message ----

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)*(O2scfm>0)*(O2scfm*diversity))

Try:

=SUMPRODUCT((Zone=B8)*(O2scfm>0), 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.