# Sum #n/a

#### bjanger

##### Board Regular
Hi,

I'm trying to sum a row of values, and then calculate the average value. In the row all zero values will be denoted #N/A. The zero values are denoted #N/A to remove the zero-entries when the values are plotted in a graph.

This is my formula for calculating the average, but i'm getting an error and i'm guessing that it has to do with ">0". How can this argument skip #N/A entries?

=SUM(O4:AS4)/(COUNTIF(O4:AS4;">0"))

BR
Bjanger

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### dafan

##### Well-known Member
Just use
Code:
``=AVERAGE(O4:AS4)``
as AVERAGE skips zero values and blanks automatically.

#### bjanger

##### Board Regular
Hi Stefan,

I still get the #N/A error using the average formula...
Some cells have values, some have 0, others have =NA() (which shows as #N/A)

-Bjanger

#### Richard Schollar

##### MrExcel MVP
Hi

Try

=SUMIF(O4:AS4,"<>#N/A")/COUNTIF(O4:AS4,"<>#N/A")

#### bjanger

##### Board Regular
Thanks, it's exactly what i needed!

BR
Bjanger

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Another way using array formula:

=AVERAGE(IF(ISNUMBER(O4:AS4),O4:AS4))

which must be confirmed with control + shift + enter (not just enter)

Replies
12
Views
197
Replies
4
Views
138
Replies
5
Views
46
Replies
0
Views
302
Replies
1
Views
74

1,191,538
Messages
5,987,172
Members
440,083
Latest member
Bluepanther

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back