sum of rows...

[James]

i have a number of rows that i want to sum, the problem is that these rows
have formula's if there is no real value. is there a way to sum the actual
values and ignore the cells that have formula's?

for example, A1=5, A2=2, A3=, (formula), A4=1.

i get an #N/A. any suggestions?

TIA

James

 

[Aladin Akyurek]

If A3 housing a formula that returns #N/A, try...

=SUMIF(A1:A4,"<>#N/A")

instead of:

=SUM(A1:A4)

 

[James]

Perfect!

thank you!!!

If A3 housing a formula that returns #N/A, try...

=SUMIF(A1:A4,"<>#N/A")

instead of:

=SUM(A1:A4)

 

[David McRitchie]

Hi James,
If you mean A3 has a formula like =IF("A" = D3,E3,"")
which would give you a #VALUE! error if you used
=A1+A2+A3+A4

you can use something in A5 like: =SUM(A1:A4)
or preferably so that you can insert more rows without having
to change your formula: =SUM(A1:OFFSET(A5,-1,0))

More on the reason for using offset in
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset