+ Reply to Thread
Results 1 to 3 of 3

Ignore Blank Cells in an array formula

  1. #1
    Registered User
    Join Date
    10-24-2018
    Location
    Auckland, New Zealand
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    6

    Ignore Blank Cells in an array formula

    I have created an array and from which I wish to select all instances of a string (entered in $A$304 in that array.

    I need help in adjusting the following array formula to ignore any blank cells it finds in the array

    {=INDEX($B$1:$AU$302,SMALL(IF($B$1:$AU$302=$A$304*($B$1:$AU$302<>""),ROW($B$1:$AU$302)-ROW(INDEX($B$1:$AU$302,1,1))+1),1))}

    Are you able to guide me in the right direction please?

    Regards Bryce

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,717

    Re: Ignore Blank Cells in an array formula

    Hi Bryce. Welcome to the forum. If you make this small adjustment I believe your formula already does that.

    =INDEX($B$1:$AU$302,SMALL(IF(($B$1:$AU$302=$A$304)*($B$1:$AU$302<>""),ROW($B$1:$AU$302)-ROW(INDEX($B$1:$AU$302,1,1))+1),1))

    Edit If that doesn't do what you expect:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Last edited by FlameRetired; 10-25-2018 at 11:50 PM.
    Dave

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,051

    Re: Ignore Blank Cells in an array formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to ignore blank cells with SUM?
    By Mr.Castle in forum Excel General
    Replies: 7
    Last Post: 08-20-2018, 05:12 PM
  2. [SOLVED] Ignore blank cells with formulas or ignore NA() in a formula
    By guiismiti in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-24-2016, 02:13 PM
  3. Ignore Blank Cells in MINIMUM array formula
    By taniwha in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-04-2013, 09:37 PM
  4. Get an array to ignore blank cells
    By moses67 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-16-2013, 11:37 AM
  5. [SOLVED] Getting an array to ignore blank cells
    By Mike001 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-25-2013, 06:46 PM
  6. [SOLVED] Array formula to ignore blank cells
    By trickeyja in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-22-2012, 07:35 PM
  7. Array formula to ignore blank cells
    By bronsonb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2011, 09:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1