Sum Range, but ignore #N/A

[peterhinton]

Morning All,

I have some data in column A like the below,

I want to sum this, however when I use =SUM(A:A) I get an error due to the #N/A,

How can I get around this,
I cant alter the data in column as its generated from a background query,

Sure there is a simple array formula to do this, any suggestions ?

31666.67
44833.33
33666.67
33666.67
33666.67
9166.667
8083.333
8833.333
1000
9500
#N/A

<colgroup><col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[MARZIOTULLIO]

	a​	b​	c​
1​	31666.7​		214083.3​
2​	44833.3​
3​	33666.7​
4​	33666.7​
5​	33666.7​
6​	9166.67​
7​	8083.33​
8​	8833.33​
9​	1000​
10​	9500​
11​	#n/a​

<tbody>
</tbody>

C1=SUM(IF(ISERROR(A1:A11),"",A1:A11)) control +shift+enter

 

[steve the fish]

Maybe:

=AGGREGATE(9,6,A:A)

or

=SUMIFS(A:A,A:A,"<>#N/A")

 

[peterhinton]

=SUMIFS(A:A,A:A,"<>#N/A")

Sooooo simple, I need to give my head a wobble, Thanks