Ignore cell errors

[hugh]

Hello there, probably a really simple one, how do I add up a list of numbers ensuring the formula ignores any cells which display an error? I need certain cells to contain errors due to the formatting I am working on

 

[NARAYANK991]

Hi Hugh ,


Try this :


=SUM(IF(ISERROR(range),0,range))


entered as an array formula , using CTRL SHIFT ENTER.


range is your list of numbers e.g. B17:B37.


Narayan

 

[hugh]

Narayan


Perfect - thank you!


Hugh

 

[Colin Legg]

You can also use SUMIF which avoids the need to use an array formula, eg

[pre]

=SUM(SUMIF(A2:A10,{"<0",">0"}))

[/pre]

 

[Colin Legg]

I was reading another thread and it reminded me that AGGREGATE() is also an option to do this if you are using Excel 2010 or later:

[pre]

=AGGREGATE(9,6,A2:A10)

[/pre]
The 9 stands for SUM. The 6 stands for ignore error values.