+ Reply to Thread
Results 1 to 3 of 3

How to ignore blanks in an Index/Match Array Formula

  1. #1
    Registered User
    Join Date
    12-02-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    1

    How to ignore blanks in an Index/Match Array Formula

    Hi everyone,
    I'm using the following formula to look at a range of cells and return the most common text entry. The formula works fine as long as there is text; if there are more blank cells than entries, then it returns a 'blank' and my formula cell is empty. Does anyone have a pointer on how to clean this up so that it ignores blank cells?

    =INDEX(C2:C11,MATCH(MODE(COUNTIF(C2:C11,C3:C11)),COUNTIF(C2:C11,C2:C11),0))

    Thanks!

    MC

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to ignore blanks in an Index/Match Array Formula

    Try this array formula**:

    =INDEX(C2:C11,MODE(IF(C2:C11<>"",MATCH(C2:C11,C2:C11,0))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to ignore blanks in an Index/Match Array Formula

    x-post @
    http://www.mrexcel.com/forum/excel-q...h-formula.html
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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] how to ignore text in a sumproduct, index, and match formula.
    By sbham in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-12-2013, 04:55 PM
  2. [SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete
    By rls231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:13 PM
  3. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 AM
  4. Replies: 3
    Last Post: 09-17-2010, 05:59 AM
  5. Median as an Array does not ignore blanks
    By ctbrian in forum Excel General
    Replies: 8
    Last Post: 05-06-2010, 03:12 PM

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