Can't add up

Hello all – sorry for the long post

I think that my problem is two fold – in the range G6 – G33 I have a drop down lists containing the following values: “FF”, “PF”, and “ZF”. In the range A6 – A33 I have used the following formula ‘=LOOKUP(G6,{"FF","PF","ZF"},{"3","2","1"})’ to change the letters to numbers so that I can add them up. The formula changes the cell reference as it goes down. This seems to be working. Where these are no letter values in the “G” range I get the #NA result in the A range, I would rather not but I can live with it.

The real problem is when I try and add using the sum function A6 – A33, in cell G34. It will not add up I get ‘0’ in cell G34. I have tried the formula =SUMIF(A6:A33,">0") in an attempt to get excel to only add the numbers.

Any ideas?

Thanks for any help.

=SUMIF(A6:A33,"<>#N/A")

or change Lookup to:

=IF(G6="","",LOOKUP(G6,{"FF","PF","ZF"},{"3","2","1"}))

you could either use
=SUMIF(A6:A3,"<>#N/A")
or
=IF(ISNA(LOOKUP(G6,{"FF","PF","ZF"},{3,2,1})),0,LOOKUP(G6,{"FF","PF","ZF"},{3,2,1}))

I understood it as if there are no letters in G6, then you want a blank (or 0) instead of #N/A error....