SUM - but ignoring text in the data range

[carol]

Does anyone know how to get the total of a data range which consists of a mix
of values and #VALUE! text. The cells in the data range are using an INDEX
formula, so I want to ignore the cells that don't generate a value, but count
up the ones that did generate a value. (Without manually selecting the 'good'
cells).

Any ideas?

Thanks in advance.

 

[Niek Otten]

Change the formulas in the cells to be summed:

=IF(ISERROR(YourFormula),"",YourFormula)

Or use a helper column to avoid evaluating all formulas twice.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Does anyone know how to get the total of a data range which consists of a mix
| of values and #VALUE! text. The cells in the data range are using an INDEX
| formula, so I want to ignore the cells that don't generate a value, but count
| up the ones that did generate a value. (Without manually selecting the 'good'
| cells).
|
| Any ideas?
|
| Thanks in advance.

 

[Mike H]

One way

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

Which is an array formula so commit with
Ctrl+Shift+Enter.

Mike

 

[JE McGimpsey]

In general, allowing errors as expected values is terrible practice - it
masks other (real) errors, and it desensitizes the user to error values.

Better to trap the #VALUE! errors in the first place. For instance, if
the #VALUE! error is caused by

=A1 + B1

where A1 or B1 may contain text, use

=IF(COUNT(A1:B1)=2,A1+B1,"")

or

=SUM(A1,B1)

since SUM() ignores text.

If you trap the errors first, you can use SUM() to total your range.