ISERROR function

[Len]

Hi,


How to use ISERROR or SUM+IF+ISERROR function to ignore cell error
while adding up the value in the selected cells ?
e.g
A B C D
1 100.00 #N/A #N/A 845.00
2 38412100 31789400 34214600 34214600
3 100.00 #N/A #N/A #N/A
4 #N/A C 34182.770 34182.770
5 36479.1450 #N/A #N/A #N/A
6 #N/A 245.00 50.00 140.00
7 #N/A 174.00 #N/A #N/A

200.00 174.00 - 985.00
Note (a) (b) (c) (d)

Note
(a) excel formula to add up the selected cells ( ie A1+A3+A7) and the
result is 200.00
(b)excel formula to add up the selected cells ( ie A1+A3+A7) and the
result is 174.00
(c)excel formula to add up the selected cells ( ie A1+A3+A7) and the
result is 0.00
(d)excel formula to add up the selected cells ( ie A1+A3+A6+A7) and
the result is 985.00

Please help me to solve the above problem
Thanks

Regards
Len

 

[Len]

Sorry, typo error in Note (b),(c),(d) and should be

(b) excel formula to add up the selected cells ( ie B1+B3+B7) and the
result is 174.00
(c) excel formula to add up the selected cells ( ie C1+C3+C7) and the
result is 0.00
(d) excel formula to add up the selected cells ( ie D1+D3+D6+D7) and
the result is 985.00

 

[º¼ÖÝÈË]

=SUM(IF(ISERROR(A1),0,A1),IF(ISERROR(A3),0,A3),IF(ISERROR(A6),0,A6))

 

[Tushar Mehta]

The usual approach would be the array formula
=SUM(IF(ISERROR(A1:A7),,A1:A7))

But, in your case, you also have those large numbers in rows 2 and 5
that you apparently want to ignore. If they are text then the above
formula will work.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

=IF(ISERROR(A1),0,A1)+IF(ISERROR(A3),0,A3)+IF(ISERROR(A7),0,A7)

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Len]

Hi,

Thanks for all of your reply

It works.

Regards
Len