I have a spread sheet that I want to enter text in a field that is being used
in a SUM. I want Excel to ignore the text and treat it as Missing Values (or
zero). The SUM currently gets a #VALUE! error.

sum should ignore text. there is most likely #VALUE in your data. you could
correct the error or try:

=SUM(IF(ISNUMBER(A1:A100), A1:A100))

array entered using Cntrl+Shift+Enter


"BruceA" wrote:

I have a spread sheet that I want to enter text in a field that is being used
in a SUM. I want Excel to ignore the text and treat it as Missing Values (or
zero). The SUM currently gets a #VALUE! error.

Or the non array formula

=SUMIF(A1:A100,"<="&99^99)




--
Regards,

Peo Sjoblom



"JMB" wrote in message
...
sum should ignore text. there is most likely #VALUE in your data. you
could
correct the error or try:

=SUM(IF(ISNUMBER(A1:A100), A1:A100))

array entered using Cntrl+Shift+Enter


"BruceA" wrote:

I have a spread sheet that I want to enter text in a field that is being
used
in a SUM. I want Excel to ignore the text and treat it as Missing Values
(or
zero). The SUM currently gets a #VALUE! error.

Hi there! I can definitely help you with that.

To tell Excel to ignore invalid numeric data, you can use the IFERROR function. This function allows you to specify what value should be returned if a formula results in an error. In your case, you want to return a zero if the formula results in an error.

Here are the steps to use the IFERROR function:

1. Select the cell where you want to enter the formula.
2. Type the formula you want to use, such as =SUM(A1:A10).
3. Wrap the formula in the IFERROR function, like this: =IFERROR(SUM(A1:A10),0).
4. Press Enter to complete the formula.

Now, if any of the cells in the range A1:A10 contain text or other invalid data, the formula will return a zero instead of the #VALUE! error.

I hope that helps!