Developer Forum

by **Darin Kram** » Tue, 08 Mar 2005 23:29:19

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.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!

by **SyBEYWxlc** » Wed, 09 Mar 2005 00:05:03

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(A1:D1,">0")+SUMIF(A1:D1,"<0")

by **QW1hbmR** » Wed, 09 Mar 2005 00:05:06

Hi
Try SUM(IF(NOT(ISERROR(A1:D1)),A1:D1))

HTH
AMANDA

by **Tom Ogilv** » Wed, 09 Mar 2005 00:21:59

=SUMIF(D1:D10,"<>#N/A")

might work. (did for me)

--
Regards,
Tom Ogilvy

by **Darin Kram** » Wed, 09 Mar 2005 01:32:02

Thanks Tom!

And if the cells are non adjacent...?

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

by **Tom Ogilv** » Wed, 09 Mar 2005 07:34:46

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.

--
Regards,
Tom Ogilvy

Developer Forum

Summing N/A's

Summing N/A's

Summing N/A's

Summing N/A's

Summing N/A's

Summing N/A's

Summing N/A's