Sum with #N/A listed

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,
 
P

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,
 
J

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

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

Jacob Skaria

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top