+ Reply to Thread
Results 1 to 7 of 7

Need help understanding why an index match array with max(sumif(... works

  1. #1
    Registered User
    Join Date
    10-10-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Need help understanding why an index match array with max(sumif(... works

    Hi Excel Forum Help,

    With the following data, I am trying to answer "who sold the most total units, and who had the most total revenue?". I have the formula, but would appreciate your help understanding why the formula works.

    The solutions are:
    Most total units {=INDEX(D:D,MATCH(MAX(SUMIF(D:D,D:D,B:B)),SUMIF(D:D,D:D,B:B),0))}
    Most total revenue {=INDEX(D:D,MATCH(MAX(SUMIF(D:D,D:D,C:C)),SUMIF(D:D,D:D,C:C),0))}

    Columns A = Date
    Column B = Units
    Column C = Revenue
    Column D = Name of rep

    My questions specifically are:
    1 Specifically, what does it mean when you use (MAX(SUMIF(D:D,D:D,B:B)),SUMIF(D:D,D:D,B:B),0) as the arguments for MATCH?
    2 What does it mean when the criteria for the sumif is also its range in the context of an array?
    3 I understand the structure of Index + Match, but don't understand why the SUMIFs work as the arguments for Match.
    4 Why does this only work as an array function?

    Thank you so much! Appreciate your enlightenment.

  2. #2
    Registered User
    Join Date
    10-10-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Re: Need help understanding why an index match array with max(sumif(... works

    Sorry for the double post, see attached for the data set.
    Attached Files Attached Files

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Need help understanding why an index match array with max(sumif(... works

    I'll let you make an investment in the answer. Select E3:E66 and enter

    {=SUMIF(D3:D66,D3:D66,B3:B66)}

    Look at the results; what is it calculating?
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    10-10-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Re: Need help understanding why an index match array with max(sumif(... works

    Thanks for pointing me in the right direction, much appreciated. Hoping you can validate whether my understanding is correct, along with a follow up question (and if you would like to challenge me by answering this with another question):

    {=SUMIF(D3:D66,D3:D66,B3:B66)} finds the sum of units by rep name. By putting the criteria equal to the range, this cycles the sumif array formula through the entire range, as opposed to individual criterias against the range.

    Substituting the full formula now for values,
    INDEX(D3:D66,MATCH(527,[496,527,513,480,496...])

    Why does typing in the formula by selecting the adjacent columns (E3:E66) produce different results than if I just retyped the formula into each cell individually? Is this just what the formula is doing now? (Reworded differently, why does the single typed version of the formula only return for the first element in the array?)

    Thank you again, and really appreciate your insight!

    -Joseph

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Need help understanding why an index match array with max(sumif(... works

    Why does typing in the formula by selecting the adjacent columns (E3:E66) produce different results than if I just retyped the formula into each cell individually?
    Because the formula returns an array of results. If you enter it singly in each cell, you only see the first value in the array.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Need help understanding why an index match array with max(sumif(... works

    BTW, I think this would be a more content-intensive presentation:

    G
    H
    I
    2
    Rep
    Units
    Rev
    3
    Joe
    496
    1635
    4
    Mark
    528
    1600
    5
    Bob
    512
    1600
    6
    Carl
    480
    1500
    7
    ALEX
    64
    100
    Last edited by shg; 10-10-2017 at 09:14 PM.

  7. #7
    Registered User
    Join Date
    10-10-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Re: Need help understanding why an index match array with max(sumif(... works

    Thank you, I do agree this looks much better...

+ 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] This formula works but I donīt understand why Index(Index) match
    By campelliann in forum Excel General
    Replies: 2
    Last Post: 01-25-2016, 05:55 PM
  2. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  3. [SOLVED] Need a SUMPRODUCT/SUMIF/INDEX/MATCH array formula. Please Help!!
    By TheClaw2323 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2015, 01:34 PM
  4. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  5. [SOLVED] Lack of understanding Index and match, and small formulas
    By lreed in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-10-2014, 11:37 AM
  6. array match formula fails but simple match works
    By dimwit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2014, 07:30 PM
  7. Replies: 0
    Last Post: 05-15-2013, 05: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