SUM Function over some #N/A cells

[Crusher]

If I am using the following function:

=SUM(B1:B50)

most of those cells contain numbers but a few might be contain a valu
of #N/A or #Value! therefore the results is #N/A or #VALUE! for thi
function. I want the total of the actual numbers and ignore th
errors.

What can I do

 

[Chip Pearson]

Use the following array formula

=SUM(IF(ISERROR(B1:B50),0,B1:B50))

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Frank Kabel]

Hi
one way:
=SUMIF(B1:B50,"<>#NA")

 

[Peter Atherton]

-----Original Message-----
If I am using the following function:

=SUM(B1:B50)

most of those cells contain numbers but a few might be contain a value
of #N/A or #Value! therefore the results is #N/A or #VALUE! for this
function. I want the total of the actual numbers and ignore the
errors.

Crusher
Try this

=SUM(IF(ISNUMBER(B1:B50),B1:B50)) entered as an array Ctrl
+ SHIFT + Enter

Peter Atherton