# Thread: Summing values without N/A (2000)

1. ## Summing values without N/A (2000)

I think I have had a formula regression or reversion back to a pre-brain functioning period and I cannot, for the life of me, figure out how to sum the cells in B4:B13 where the contents are not "#N/A"

If have tried at least 15 different versions of =IF(NOT(ISNA(B4:B13)),SUM(B\$:B13))) and other such. Any help?

2. ## Re: Summing values without N/A (2000)

One possible solution
=SUMIF(B4:B13,">0")+SUMIF(B4:B13,"<0")

3. ## Re: Summing values without N/A (2000)

Mike

Try this:

=SUMIF(B4:B13,"<>#N/A")

Brent

4. ## Re: Summing values without N/A (2000)

Thanks, both of those look like they should work...guess it was too late last nite (or too early this morning I should say)!

5. ## Re: Summing values without N/A (2000)

More general approach avoiding all errors is to use an array formula:

=SUM(IF(ISERROR(Range),"",Range))

Which of course can easily be adapted to:

=AVERAGE(IF(ISERROR(Range),"",Range))
=STDEV(IF(ISERROR(Range),"",Range))

All Array entered with control-shift-enter.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•