Ignoring N/A

[pedroc]

I have a column in which N/A could acceptably be returned. However I need to total the values that appear in the column. Can I create a sum which just ignores the presence of the N/A?

Many thanks

pedroc

 

[SydneyGeek]

To sum (for example) B2:B29 --

=SUM(IF(ISNA(B2:B29),0,B2:B29))

Confirm with CTRL+SHIFT+ENTER

Denis

 

[philippa79]

Hi pedroc

When you use the SUM function, it will only add together the numbers within that range and will ignore any text. Therefore if you have a range of data which is part numbers and part 'N/A' then the numbers will be added but the text ignored.

Hope this helps!

 

[p45cal]

In the cells that produce the #N/A change the formula to:
=IFERROR(ExistingFormulaHere,"n/a")

where you should replace ExistingFormulaHere with the obvious.

 

[rs2k]

You could try 'ISNA' in your formula;
This is a simple VLOOKUP formula using the ISNA;

=IF(ISNA(VLOOKUP(D1,$A$1:$B$6,2,0)),"",VLOOKUP(D1,$A$1:$B$6,2,0))

HTH
Colin