SumIf with NA
 

A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

Hi,

Try this

=SUM(IF(NOT(ISERROR(A1:A3)),A1:A3))

This is an array which must be entered with CTRL+Shift+Enter and not just
enter. If you do it correctly Excel will put curly brackets around the
formula {}. You can't type these yourself.

Mike

"ArthurJ" wrote:

A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

use =Sumif(A1:A4,"0")



On Oct 24, 7:31*pm, ArthurJ wrote:
* * * * A * * * *
1 * * 10
2 * *#N/A
3 * * 20
4 * *#N/A
5 * =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

Couple of ways:

=SUMIF(A1:A4,"<#N/A")
OR
=SUMIF(A1:A4,"<"&99^99)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ArthurJ" wrote in message
...
A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

Hi,

Apologies I should have looked more carefully at your formula instead

=SUMIF(A1:A3,"<#N/A")

This time not an array.

Mike

"Mike H" wrote:

Hi,

Try this

=SUM(IF(NOT(ISERROR(A1:A3)),A1:A3))

This is an array which must be entered with CTRL+Shift+Enter and not just
enter. If you do it correctly Excel will put curly brackets around the
formula {}. You can't type these yourself.

Mike

"ArthurJ" wrote:

A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

Should mention that the second suggestion:

=SUMIF(A1:A4,"<"&99^99)

Will by-pass all errors, not just the #N/A error.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"RagDyeR" wrote in message
...
Couple of ways:

=SUMIF(A1:A4,"<#N/A")
OR
=SUMIF(A1:A4,"<"&99^99)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ArthurJ" wrote in message
...
A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

Thanks all of you!

"ArthurJ" wrote:

A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art

SumIf with NA
 

Trype or paste in A5
=SUM(IF( ISNA(A1:A4),0,A1:A4))
then press CTRL-SHIFT-ENTER

"ArthurJ" wrote:

A
1 10
2 #N/A
3 20
4 #N/A
5 =SumIf(A1:A4,"<NA")

Some cells in a column of numbers evaluate to #N/A. I would like to sum the
column but ignore the #N/A entries. Is there a way to do this WITHOUT
creating a separate criteria column? I have tried various versions of the
syntax in cell A5 above without success.

Thank you,
Art