F1 = 2
G1 = x

=SUMPRODUCT(F1,G1) = #VALUE!
=SUMPRODUCT(F1:F2,G1:G2) = 0
=SUMPRODUCT({2},{"x"}) = 0

It appears that the arguments to SUMPRODUCT (in its native form) must be
arrays. Internally, it must not recognize (F1,G1) as arrays but when you
force the arrays by using { } it works. However, this seems to only apply
when there are multiple arguments.

=SUMPRODUCT(F1) = 2
=SUMPRODUCT(G1) = #VALUE!


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
All right folks ... can anyone explain this to me?

*Excluding* the first calc of
C8*'Data Tables'!B3,

This allows the OP to have text and/or nulls in any of the other cells:

=SUMPRODUCT('Data Input Form'!D8:I8,'Data Tables'!C3:H3)

And still return values without any errors.

Now, trying to duplicate the results of "no errors" for the first calc,
C8*'Data Tables'!B3

=SUMPRODUCT('Data Tables'!B3,C8)
*Doesn't* work!

If you make it into a range, it *does work* (accepts text):
=SUMPRODUCT('Data Tables'!B3:B4,C8:C9)

Tried fooling it with something like this:

=SUMPRODUCT('Data Tables'!B3:B3,C8:C8)

But that didn't work either.

What is it with the necessity of an actual range to make it accept
non-numeric values?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value.
In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.