# SUM function with an IF statement???

#### grogzy

##### New Member

I have a cell range which needs to be added up, but if all cells in the renge contain "n/a" then i'd like to display that instead.....can this be done????

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### DonkeyOte

##### MrExcel MVP
Question is a little vague but do you mean something like this ?

=IF(COUNT(A1:A10)=0,"N/A",SUM(A1:A10))

#### grogzy

##### New Member
YES!!! this is exactly what i was trying to do......thanks lasw10

But now in another cell i need to find the average of the output figure (or if the output is "n/a", then this should be displayed....

can this be done?

#### Stormseed

##### Banned

I have a cell range which needs to be added up, but if all cells in the renge contain "n/a" then i'd like to display that instead.....can this be done????

What if any of the cells contain #N/A and not the whole range ?

Assuming the range A1:A8, perhaps,

PHP:
``=IF(COUNTIF(A2:A8,"#N/A"),"#N/A",SUM(A2:A8))``

#### DonkeyOte

##### MrExcel MVP
well assuming the prior formula was returned in say cell B1 you could just use the following in average cell:

=IF(\$B\$1="N/A","N/A",\$B\$1/COUNT(\$A\$1:\$A\$10))

#### Stormseed

##### Banned
try,

Confirm the formula with Ctrl+Shift+Enter and not just Enter:
PHP:
``=AVERAGE(IF(A1:A10<>0,A1:A10))``

#### DonkeyOte

##### MrExcel MVP
stormseed, you wouldn't need the test of 0, the following (as array) would also work.

=AVERAGE(IF(A1:A10,A1:A10))

#### Stormseed

##### Banned
well assuming the prior formula was returned in say cell B1 you could just use the following in average cell:

=IF(\$B\$1="N/A","N/A",\$B\$1/COUNT(\$A\$1:\$A\$10))

Ok, I never thought about that ! you worked out the next formula considering upon the adjacent cell's output value. Your solution is always precise and clear, Luke. I guess, I have to learn a lot from you as well

#### Stormseed

##### Banned
stormseed, you wouldn't need the test of 0, the following (as array) would also work.

=AVERAGE(IF(A1:A10,A1:A10))

Luke, as the OP indicates, he has N/A in a few cells in his cell range ?

#### grogzy

##### New Member
Thanks for all the help guys....my life is no longer a misery

Replies
2
Views
166
Replies
3
Views
263
Replies
3
Views
225
Replies
0
Views
185
Replies
5
Views
200

1,191,117
Messages
5,984,739
Members
439,906
Latest member
Sekiro1899

### 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