Need help to shorten array formula in MS Excel

Posted on 2007-10-04
Last Modified: 2010-05-18
Is there a shorter way of stating the following array formula in MS Excel:

Would the use of OR be possible?
Question by:VHT
    LVL 33

    Expert Comment

    Did you tried something like:

    Keep in mind that you don't need the ARRAY keys for this

    LVL 85

    Assisted Solution

    by:Rory Archibald
    You can use:
    also entered normally.
    LVL 33

    Accepted Solution

    Even better, forgot about entering the array in SUMPRODUCT.
    Indeed my function should be :

    made a typo due to all the conditions
    LVL 6

    Assisted Solution


    Another way to shorten this formula further and enhance readability is by naming your ranges (see Excel help for details on this).

    If you name $I$2:$I$25017 = "One" and name $BN$2:$BN$25017 = "Two" your formula would be (I would suggest you actually use meaningful names when you implement this in your workbook):


    Hope that helps,

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now