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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[K Dales]

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")

 

[Amanda]

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[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.