Summing N/A's

[Darin Kramer]

Howdie...

Problem...
A1 =5
b1 = #N/A result of formulae
C1 = 3
D1 = 2
Etc etc.

I need to sum a1 to D1 to get 10
The N/A may be in any one of the cells...

If statement the soln...?

Thanks

D

 

[Guest]

While the normal SUM() function will error out if there is an error value in
the range SUMIF does not, so you can do it like this:
=SUMIF(A11,">0")+SUMIF(A11,"<0")

 

[Guest]

Hi
Try SUM(IF(NOT(ISERROR(A11)),A11))


HTH
AMANDA

 

[Tom Ogilvy]

=SUMIF(D110,"<>#N/A")

might work. (did for me)

 

[Darin Kramer]

Thanks Tom!

And if the cells are non adjacent...?

 

[Tom Ogilvy]

That would be harder. Is there a pattern involved?

If it is blocks of cells but no pattern, you could always use the summation
sign (+) linking separate sumif formulas.