Sum with #N/A listed

  • Thread starter Thread starter Pran
  • Start date Start date
P

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,
 
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,
 
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
 
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.
 
Biff carry on.. Since there was no response for sometime, I thought you are
off for the day..and hence responded..
 
I thought you are off for the day

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

Maybe I should go on strike!
 
Back
Top