ignore #N/A in a sum?
 

I have a worksheett that has many #N/A's where a matching formula brought
over data. It there is no match I get another N/A's. Thats ok, but I need
to sum accross the rows and I don't want to remove the formula.

I am adding three columns +F160+G160-H160

Is there a formula I can use? Or do I really need to delete all the N/A's.
( I will just have to put them back later if I do).


Thanks,


Todd

ignore #N/A in a sum?
 

I am adding three columns +F160+G160-H160

Doesn't look like you're adding H160?

=SUMIF(F160:H160,"<#N/A")

Biff

"Todd" wrote in message
...
I have a worksheett that has many #N/A's where a matching formula brought
over data. It there is no match I get another N/A's. Thats ok, but I
need
to sum accross the rows and I don't want to remove the formula.

I am adding three columns +F160+G160-H160

Is there a formula I can use? Or do I really need to delete all the
N/A's.
( I will just have to put them back later if I do).


Thanks,


Todd

ignore #N/A in a sum?
 

=if(isnumber(f160),f160,0)
+if(isnumber(g160),g160,0)
-if(isnumber(h160),h160,0)

Is one way.



Todd wrote:

I have a worksheett that has many #N/A's where a matching formula brought
over data. It there is no match I get another N/A's. Thats ok, but I need
to sum accross the rows and I don't want to remove the formula.

I am adding three columns +F160+G160-H160

Is there a formula I can use? Or do I really need to delete all the N/A's.
( I will just have to put them back later if I do).

Thanks,

Todd

--

Dave Peterson

ignore #N/A in a sum?
 

Todd

Why have you many cells with #N/A's? Vlookups with no data or incorrect data?

Trap for them in the formulas that create them, not in the SUM formula.

=IF(ISNA(VLOOKUP(D1,$B$1:$C$29,2,FALSE)),"",VLOOKU P(D1,$B$1:$C$29,2,FALSE))


Gord Dibben MS Excel MVP

On Tue, 5 Sep 2006 13:38:01 -0700, Todd wrote:

I have a worksheett that has many #N/A's where a matching formula brought
over data. It there is no match I get another N/A's. Thats ok, but I need
to sum accross the rows and I don't want to remove the formula.

I am adding three columns +F160+G160-H160

Is there a formula I can use? Or do I really need to delete all the N/A's.
( I will just have to put them back later if I do).


Thanks,


Todd