ExcelBanter - SUM When value is NA

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - SUM When value is NA (https://www.excelbanter.com/excel-discussion-misc-queries/202957-sum-when-value-na.html)

Paula

SUM When value is NA

Hi, I can't find an existing post to answer my question - would appreciate
some advice.

I have columns which sometimes contain N/A# - my sum at the end when it sees
this then enters N/a in the total, I need a formula to say if I have the
following values

N/A
300
100
200 It would sum up all the numbers in the column ignoring the N/A, can it
be done?
Many thanks
Paula

Mike H

SUM When value is NA

Paule,

Try this

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

Mike

"Paula" wrote:

Hi, I can't find an existing post to answer my question - would appreciate
some advice.

I have columns which sometimes contain N/A# - my sum at the end when it sees
this then enters N/a in the total, I need a formula to say if I have the
following values

N/A
300
100
200 It would sum up all the numbers in the column ignoring the N/A, can it
be done?
Many thanks
Paula

Paula

SUM When value is NA

Hi Mike,

Perfect, thanks for that, you've helped me before actually, appreciate your
great advice!!

Regards
Paula

"Mike H" wrote:

Paule,

Try this

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

Mike

"Paula" wrote:

Hi, I can't find an existing post to answer my question - would appreciate
some advice.

I have columns which sometimes contain N/A# - my sum at the end when it sees
this then enters N/a in the total, I need a formula to say if I have the
following values

N/A
300
100
200 It would sum up all the numbers in the column ignoring the N/A, can it
be done?
Many thanks
Paula

Mike H

SUM When value is NA

Glad I could help

"Paula" wrote:

Hi Mike,

Perfect, thanks for that, you've helped me before actually, appreciate your
great advice!!

Regards
Paula

"Mike H" wrote:

Paule,

Try this

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

Mike

"Paula" wrote:

Hi, I can't find an existing post to answer my question - would appreciate
some advice.

I have columns which sometimes contain N/A# - my sum at the end when it sees
this then enters N/a in the total, I need a formula to say if I have the
following values

N/A
300
100
200 It would sum up all the numbers in the column ignoring the N/A, can it
be done?
Many thanks
Paula

All times are GMT +1. The time now is 05:52 AM.