+ Reply to Thread
Results 1 to 3 of 3

SUMIFS or Array Index Match - which is better?

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    SUMIFS or Array Index Match - which is better?

    Hi - I have a large table and I need to find two criteria (year and name) and return the number on the same line in column B.
    I can do this two ways, one with SUMIFS (even though I'm not actually summing anything) or an Index/Match Array.

    I have to do this in a lot of different ways throughout the workbook and I want it to be as efficient as possible regarding calculations.

    Which is better/faster?

    {=IFERROR(INDEX(Table04[[#All],[Tution and Fees per FTE Student]],MATCH(P$9,IF(Table04[[#All],[YEAR]]=$H10,Table04[[#All],[Institution Name]]),0)),0)}


    =SUMIFS(Table04[[#All],[Tution and Fees per FTE Student]],Table04[[#All],[Institution Name]],Q$9,Table04[[#All],[YEAR]],$H10)

    Thank you!

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: SUMIFS or Array Index Match - which is better?

    In my experience, if I'm using array formulas for a lot of cells (>2000 cells), then I find a big slow down in the calculation.

    I haven't really tested how SUMIFS differ, but these new formulas also use the array formula concept, so I would also think it would slow down the calcs.

    However, I would still say, SUMIFS will be faster for some reason I could not tell you.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,027

    Re: SUMIFS or Array Index Match - which is better?

    SUMIFS is much better
    Never use Merged Cells in Excel

+ 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] Sumifs With Index/Match Column
    By benno87 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-03-2015, 08:14 AM
  2. SumIFS or Index Match Help
    By excelnovice936 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2013, 12:08 AM
  3. Replies: 3
    Last Post: 04-23-2013, 03:46 PM
  4. Replies: 0
    Last Post: 01-18-2011, 04:30 PM
  5. SUMIFS, INDEX, and MATCH-- how to fix?
    By sofib09 in forum Excel General
    Replies: 6
    Last Post: 12-13-2010, 11:05 AM

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