trying to sum columns in which some cells read n/a

[Guest]

I have a spreadsheet showing the results of 3 sales contests. I have pulled
data using V-look-up and am now trying to sum $ totals for each individual.
However, summing is not working because cells contain n/a. How do I work
around this?
Example:

Name contest 1 contest 2 contest 3 Total pay
Jane $300 N/A $200
Bill N/a $500 N/A
Sandra $200 N/a n/A

When I try to sum Jane's totals I end up with N/A because there is an N/A in
the column range.

Can anyone help?

Thanks!

 

[Guest]

Let's say Jane's data is in cells B22

=SUM(IF(ISNUMBER(B22),B22))

Commit with CTRL SHIFT ENTER

 

[OZDOC]

You will need to look at the formula in your v lookup
i.e. =if (iserror( your formula),0,your formula))
or look and see if the cell is blank if so place a zero in the cell if not
your formula

if you post the formula may be able to help more
Pete

 

[Biff]

Hi!

N/A is TEXT and should not cause a problem.

#N/A , on the other hand, will cause a problem!

Try this:

=SUMIF(B11,"<>#N/A")

Biff