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!

Let's say Jane's data is in cells B2:D2

=SUM(IF(ISNUMBER(B2:D2),B2:D2))

Commit with CTRL SHIFT ENTER


"Camaro" wrote:

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!

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

"Camaro" wrote in message
...
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!

Hi!

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

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

Try this:

=SUMIF(B1:D1,"<#N/A")

Biff

"Camaro" wrote in message
...
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!