SUM Ignoring Errors

[mouse88]

I need to sum up several numbers and replace any errors in the sum with 0. The following formula works but is too long in my opinion.

Anyone know of a better way to do this?

Am I better off just writing my own custom function to take care of this for me?

Thanks

Matt

=SUM(IF(ISERROR('Agent Performance'!D10),0,'Agent Performance'!D10)+IF(ISERROR('Agent Performance'!D29),0,'Agent Performance'!D29)+IF(ISERROR('Agent Performance'!D48),0,'Agent Performance'!D48)+IF(ISERROR('Agent Performance'!D67),0,'Agent Performance'!D67)+IF(ISERROR('Agent Performance'!D86),0,'Agent Performance'!D86)+IF(ISERROR('Agent Performance'!D105),0,'Agent Performance'!D105)+IF(ISERROR('Agent Performance'!D124),0,'Agent Performance'!D124)+IF(ISERROR('Agent Performance'!D143),0,'Agent Performance'!D143)+IF(ISERROR('Agent Performance'!D162),0,'Agent Performance'!D162)+IF(ISERROR('Agent Performance'!D181),0,'Agent Performance'!D181)+IF(ISERROR('Agent Performance'!D200),0,'Agent Performance'!D200)+IF(ISERROR('Agent Performance'!D219),0,'Agent Performance'!D219)+IF(ISERROR('Agent Performance'!D238),0,'Agent Performance'!D238)+IF(ISERROR('Agent Performance'!D257),0,'Agent Performance'!D257)+IF(ISERROR('Agent Performance'!D276),0,'Agent Performance'!D276)+IF(ISERROR('Agent Performance'!D295),0,'Agent Performance'!D295)+IF(ISERROR('Agent Performance'!D314),0,'Agent Performance'!D314)+IF(ISERROR('Agent Performance'!D333),0,'Agent Performance'!D333)+IF(ISERROR('Agent Performance'!D352),0,'Agent Performance'!D352)+IF(ISERROR('Agent Performance'!D371),0,'Agent Performance'!D371)+IF(ISERROR('Agent Performance'!D390),0,'Agent Performance'!D390)+IF(ISERROR('Agent Performance'!D409),0,'Agent Performance'!D409)+IF(ISERROR('Agent Performance'!D428),0,'Agent Performance'!D428)+IF(ISERROR('Agent Performance'!D447),0,'Agent Performance'!D447)+IF(ISERROR('Agent Performance'!D466),0,'Agent Performance'!D466))

 

[jasonb75]

This doesn't allow for inserting / deleting rows.

Array formula, confirm with Shift Ctrl Enter

=SUM(IF(ISNUMBER('Agent Performance'!D10:D466),IF(MOD(ROW('Agent Performance'!D10:D466)-10,19)=0,'Agent Performance'!D10:D466)))

 

[mouse88]

Would you be willing to break this up and explain it to me.

I prefer to understand how something works before I use it.

Thanks

Matt

 

[jasonb75]

Sure,

The nested functions work on a process of elimination.

IF(ISNUMBER('Agent Performance'!D10:D466),

returns TRUE for each cell in that range that holds a number, or FALSE for anything else (i.e. text or errors).

IF(MOD(ROW('Agent Performance'!D10:D466)-10,19)=0,

takes the row of each cell in the range and subracts 10, so D10 = 0, D19 = 10, D29 = 19, etc, MOD then returns the remainder of each when divided by 19, Only those divisible exactly by 19 will have a remainder of 0 which is what the formula is testing for to identify the correct rows and return TRUE.

The figures are only summed in rows where both of the above tests return TRUE, i.e. the figure is a number, not an error, and the row -10 is a multile of 19.

Hope that makes sense.

 

[pgc01]

Hi Matt

Another option:

=SUMPRODUCT(SUMIF(OFFSET('Agent Performance'!D10,(ROW(INDIRECT("1:25"))-1)*19,0),">-1e300"))