SUM function with an IF statement???

[grogzy]

Please help, as i've been having loadsa trouble with the following....

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

thanks in advance!!!

 

[DonkeyOte]

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

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

 

[grogzy]

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]

Please help, as i've been having loadsa trouble with the following....

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

thanks in advance!!!

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

Assuming the range A1:A8, perhaps,

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

 

[DonkeyOte]

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]

try,

Confirm the formula with Ctrl+Shift+Enter and not just Enter:

=AVERAGE(IF(A1:A10<>0,A1:A10))

 

[DonkeyOte]

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

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

 

[Stormseed]

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]

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]

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