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

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


Len wrote:
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

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

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

In article .com,
says...
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

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

etc.

--

HTH

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


"Len" wrote in message
ups.com...
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


Len wrote:
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

Hi,

Thanks for all of your reply

It works.

Regards
Len

Tushar Mehta wrote:
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

In article .com,
says...
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