Sum with error

[vonatron]

Hello all,

I'm having an issue trying to do what I thought was simple sum formula. I want to create a formula where I can sum the cells while ignoring the #N/A values. An additional problem is that the cells that I want to add are not consecutive.

For example, I want the sum of A1, D1, G1, J1.
However, G1=#N/A, so the total sum is coming up #N/A as well.
I do not want to place an iserror formula into each of A1, D1, G1, J1 because there are way too many cells with additional formulas.

Is there a way do to this?

Thanks!!
-Von

 

[Scott Huish]

=SUM(IF(MOD(COLUMN($A$1:$J$1)-COLUMN($A$1),3)=0,IF(ISNUMBER($A$1:$J$1),$A$1:$J$1)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Aladin Akyurek]

Hello all,

I'm having an issue trying to do what I thought was simple sum formula. I want to create a formula where I can sum the cells while ignoring the #N/A values. An additional problem is that the cells that I want to add are not consecutive.

For example, I want the sum of A1, D1, G1, J1.
However, G1=#N/A, so the total sum is coming up #N/A as well.
I do not want to place an iserror formula into each of A1, D1, G1, J1 because there are way too many cells with additional formulas.

Is there a way do to this?

Thanks!!
-Von

The relevant cells appear equally spaced. If this is true...

Ocontrol+shift+enter, not just enter:

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

 

[Matty]

Another option:

=SUM(SUMIF(INDIRECT({"A1","D1","G1","J1"}),">0",INDIRECT({"A1","D1","G1","J1"})))

Though this does employ the volatile INDIRECT function.

Matty

 

[Aladin Akyurek]

Another option:

=SUM(SUMIF(INDIRECT({"A1","D1","G1","J1"}),">0",INDIRECT({"A1","D1","G1","J1"})))

Though this does employ the volatile INDIRECT function.

Matty

Moreover and more importantly: 1. {"A1","D1","G1","J1"} requires manual construction; 2. INDIRECT also anchors (inflexibly) the relevant cells.

 

[Matty]

1. {"A1","D1","G1","J1"} requires manual construction

Granted.

2. INDIRECT also anchors (inflexibly) the relevant cells.

Indeed, this does become a problem if the OP intends to "copy down" said formula, though this wasn't mentioned in their post.

For what it's worth, I would always opt for a formula similar to what HOTPEPPER and yourself posted given equally spaced data, but I see no harm in informing the OP of an alternative option if it suits their needs.

Matty

 

[Ruddles]

I want to create a formula where I can sum the cells while ignoring the #N/A values.

Perhaps you should concentrate on getting rid of the #N/A values?