Sum with #N/A listed

[Pran]

Hi,

I want to sum colum B which contains formula for each cell which create
number as result. Several cell's result are #N/A, and i want that result also
counted (as zero)

Could you help me please...

Regards,

 

[T. Valko]

Try one of these:

=SUMIF(B1:B100,"<>#N/A")
=SUMIF(B1:B100,"<1E100")

 

[Pran]

Thx, and it works for the column!

Now, i have additional problem for the row's sum.
Still same request, but each cell in one row are separated by two column
(A1;D1;G1;J1 etc)

I tried previous formula using sumif(a1;d1;g1;j1);"<>#n/a")) but it wont work.

Regards,

 

[Jacob Skaria]

Try the below array formula to sum up a1;d1;g1;j1 ignoring #n/a

=SUM(IF(MOD(COLUMN(A1:J1),3)=1,IF(ISNA(A1:J1),0,A1:J1)))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes

 

[T. Valko]

It's bit more complicated when the range isn't a contiguous block.

Try this array formula** :

=SUM(IF(MOD(COLUMN(A1:J1)-COLUMN(A1),3)=0,IF(ISNUMBER(A1:J1),A1:J1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Jacob Skaria]

Biff carry on.. Since there was no response for sometime, I thought you are
off for the day..and hence responded..

 

[T. Valko]

I thought you are off for the day

For some reason, I seem to never get days off.

Maybe I should go on strike!