ignore error values in array formula

  • I use the following array formula, which works fine, but if there is a error value (#N/A) in column B the formula returns #N/A.


    Code
    {=IF(ISBLANK($J4),"",(SUM(($B$5:$B$2005=$J4)*($O$5:$O$2005="Yes")*$J$5:$J$2005)))}


    I tried various ways to get over this and have failed every time, any help would be much appreciated.



    Many Thanks


    Alan

  • Re: ignore error values in array formula


    Try


    =IF(ISBLANK($J4),"",(SUM(IF(NOT(ISERROR($B$5:$B$2005)),IF($B$5:$B$2005=$J4,IF($O$5:$O$2005="Yes",$J$5:$J$2005,0))))))


    array entered with CTRL+SHIFT+ENTER

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!