Ignore blank cells in array formula

  • I've got a list with productnumbers. Like this:

    All numbers are RS followed by four numbers.

    I use the following array formula to find the highest number:


    However the list goes further than B100, but I can't use B:B because there are also empty cells in column B which gives #Value!.
    How can I get this formula to ignore blanks?

  • Re: Ignore blank cells in array formula

    Something like:


    or you can create a dynamic named range, and use that range name as the range reference without having to add the IF() function...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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