Ignoring the #NA output

ckroon

Board Regular
Joined
Nov 9, 2005
Messages
60
Hey all, I searched but found no answer so I put it to you.
I need to sum up the results of formulas to do further calculations, but some cells have #NA in them if the data was not entered. I need the #NA to show up as zero, can I add this to the formula that is there already?

Thanks
 

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
You could use this array formula for your sum which would exclude the errors:

{=SUM(IF(ISERROR(Data),"",(Data)))}

You don't key the '{' you just type =SUM(IF(ISERROR(Data),"",(Data))) and use Ctrl+Shift+Enter to enter the formula.

Change 'Data' to reflect your range.

Or you can get rid of the N/A in the actual formula using if(iserror

EXAMPLE:

=(if(iserror(sum(A1:A22)),0,(sum(A1:A22)))
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,937
Try something like
Code:
=IF(ISNA(OLD_FORMULA),0,OLD_FORMULA)
or, to sum a column with #N/A's, try
Code:
=SUMIF(A1:A100,"<>#N/A")
 

ckroon

Board Regular
Joined
Nov 9, 2005
Messages
60
THanks

Oaktree that worked great. Thanks!

=SUMIF(A1:A100,"<>#N/A")
Now I can sum the columns if there are #NA's in it.
 

Forum statistics

Threads
1,078,012
Messages
5,337,724
Members
399,165
Latest member
ghoshwin

Some videos you may like

This Week's Hot Topics

Top