SUM Ignore Cell Errors

[ajones]

I have a sumif like
=SUMIF($B$24:$B$992,$B11,F$24:F$992)

However there are some cells that have errors #N/A. I would like to still sum the numbers and just ignore the errors but can't seem to get that to work.

I tried a sum product but the errors still came though.

=SUMPRODUCT( ($B$24:$B$992=$B10)*(F$24:F$992>0),F$24:F$992)

In an ideal world I would add anything like {>0, 0, <0}.

I am also looking at hiding the cell errors in the original data, but I am afraid that could be more complicated.

I tried a few searches related to this but did not find quite what I was after

thanks for the help

Alan

 

[Jonmo1]

Actually, correcting the Errors is not that difficult...

=IF(ISNA(formula),0,formula)

Where formula is your original formula (without the =)..

Example,
=VLOOKUP(A1,B:D,3,FALSE)

=IF(ISNA(VLOOKUP(A1,B:D,3,FALSE)),0,VLOOKUP(A1,B:D,3,FALSE))


Hope that helps.

 

[ajones]

Jonmo1,

Thanks for the reply however as I have some indirect functions and other things also going on I was reluctant to do what you mentioned.

I have done that many times before, but was trying not get the the formulas much longer.


The problem is you essentially dupe the formula and I did not want do that with all the indirect work I have.

thanks

Alan

 

[DonkeyOte]

you could if you feel so inclined use a UDF to do the error handling

Function F_ERR(result as variant, def as variant)
Select Case IsError(result)
     Case True
          F_ERR = def
     Case False
          F_ERR = result
End Select
End Function

then in XL

=F_ERR(INDIRECT(....),0)

where INDIRECT fails 0 will be returned.

 

[Jonmo1]

Never Mind, my thought using isnumber didn't work..