I am attempting to use the SUMPRODUCT formula to calc.
numbers in four columns. However, the range contains both
text and number and as a result I get a #Value reference.
Is there another formula that works similiar to the
SUMPRODUCT but will automatically ignore the text and
calc. the numbers only?

Thanks.

T.R.

SumProduct, if used as its native syntax requires, would ignore text
values in the range to sum. Perhaps you would want to elaborate on what
you want to calculate.

T.R. wrote:
I am attempting to use the SUMPRODUCT formula to calc.
numbers in four columns. However, the range contains both
text and number and as a result I get a #Value reference.
Is there another formula that works similiar to the
SUMPRODUCT but will automatically ignore the text and
calc. the numbers only?

Thanks.

T.R.

I would venture a guess that you're using the SumProduct function in the
form it was originally introduced into these groups, namely, with the
asterisk (*).
Using it in this form will return the #VALUE! error if it encounters
non-numeric data within the return range.
Strangely enough however, it *will* work with text that "looks" like
numbers.

If you would revise your formulas to the unary form (--), your text would
return a zero, and your numbers would be returned as numbers.

If, on the other hand, you're already using the unary form and still getting
a #VALUE! error, that means you have a cell, within the range to be
returned, that is itself generating this error.

The asterisk form is the safest to use if you're only using numbers.
It will calculate everything that looks like a number.
The unary form, on the other hand, will simply return a zero, and you'll
never know if something might be amiss, especially if you're importing
numbers from other sources.

Now, *IF* you're looking to have *both* numbers and text returned,
SumProduct is *not* the function to use.
You could use an array form of the Index and Match combination, which does
work with all data (errors not withstanding).

--

HTH,

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


"T.R." wrote in message
...
I am attempting to use the SUMPRODUCT formula to calc.
numbers in four columns. However, the range contains both
text and number and as a result I get a #Value reference.
Is there another formula that works similiar to the
SUMPRODUCT but will automatically ignore the text and
calc. the numbers only?

Thanks.

T.R.