#n/a drving me mad, need to force ignore or remove them - Help!

[Ang]

Hi,

Please help, I have tried hard to rectify this using some different formula
etc but to no avail.

I have a spreadsheet which logs visits to customers so that we can get a %
of visits which become orders, however we need this by
Team/Individual/Product etc, so gets more intense. I have broken it down as
far as possible to collate the data.

I am using formula: =IF((AND(F4="Education",H4>0)),H4,0) to get the value of
orders/team.

i.e. if F4 is the Education Team and a figure is in order column over zero
then put the figure (as this is an order).

The Team column (i.e. Education) comes from a VLOOKUP table, so therefore
they select staff initials (from drop down list) and it automatically looks
up the team name.

This works great until the blank lines, no initials selected, results in no
team name (#n/a) and #N/A's in my result - This wouldn't matter only I
cannot add up a column with them appearing, result in AutoSum is (yes you
guessed it!) #N/A!!

Am I missing something glaringly obvious? Please help, I'm so close to
finishing this one!

I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I
am using this in the correct place.

 

[John C]

Your vlookup, I am assuming, is causing the error. You could try one of two
ways.

=IF(ISNA(VLOOKUP(...,...,...,...)),0,VLOOKUP(...,...,...,...))

or, if your initials field is blank (say your initial field is D4)
Then your VLOOKUP could be:
=IF(D4="","",VLOOKUP(...,...,...,...))

 

[Niek Otten]

=IF(ISNA(YourEntireFormula),"",YourEntireFormula)

or

=IF(ISNA(F4),"",YourFormula)

 

[Mike H]

Hi,

Try this for summing with NAs in the range

=SUMIF(J4:J14,"<"&10^23)

or this

=SUMIF(J4:J14,"<>#N/A")

Mike

 

[Peo Sjoblom]

Change the vlookup formula to


=IF(ISNA(VLOOKUP(A2,$B$2:$C$100,2,0)),"",VLOOKUP(A2,$B$2:$C$100,2,0))



Note: Replace the ranges and lookup cells with whatever cells you are using

--


Regards,


Peo Sjoblom

 

[Pete_UK]

Suppose your drop-down is in D2, and you are using this value in your
lookup formula - you could have something like this:

=IF(D2="","",VLOOKUP(D2, ... etc)

Alternatively you would use ISNA like this:

=IF(ISNA(your_formula),"",your_formula)

Both of these will give you a blank instead of the error, but you can
change the "" to 0 if you prefer.

Hope this helps.

Pete