Sum range without taking #N/A into consideration

Hello All,

I need to sum a range of cells which are showing qty's and are determined by an index formula. Unfortunately for some that don't appear in the data sheet it comes back with #N/A.

Is there a way to sum the range in which this appears but make the sume not to take the #N/A's into consideration?

Appreciate any feedback while I keep searching for solution.

Simon

Hi.

If the numbers within the range (here assumed to be A1:A10) can only ever be non-negative:

=SUMIF(A1:A10,">0")

If there may also be negative numbers within the range:

=SUM(SUMIF(A1:A10,{"<",">"}&0))

Regards

Try using sumif or sumifs like
=SUMIF(A1:A5,">0",A1:A5)+SUMIF(A1:A5,"<0",A1:A5)
or
=SUM(SUMIFS(A1:A5,A1:A5,{">0","<0"}))

Hi Both,

Thank you very much for taking your time and looking into this for me.

All versions of your suggestions worked perfectly!

Appreciate your help.

Thank you.