Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore Text when calculating time | Excel Discussion (Misc queries) | |||
Problems with Ignore Other Applications | Excel Discussion (Misc queries) | |||
Ignore N/A values with an area chart | Charts and Charting in Excel | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions | |||
How do I ignore newline character/carriage return while importing | Excel Discussion (Misc queries) |