+ Reply to Thread
Results 1 to 10 of 10

Help please? Sumif + index + match not working correctly

  1. #1
    Registered User
    Join Date
    04-15-2021
    Location
    Fall City, WA
    MS-Off Ver
    Office 365
    Posts
    6

    Help please? Sumif + index + match not working correctly

    Hi there - If you have a moment to help me out I would greatly appreciate it. I am missing something simple or maybe I need to completely change the formula.

    Attached is a simple spreadsheet (the #'s are irrelevant I am making them up). I am trying to have Excel find the Quarter and the Revenue type along the top and match them, then provide me that #.

    I assume that it fails because there are multiple columns with the same heading (NEW is repeated in row 2) and (Q1'21, Q2'21 etc is repeated in row 3). Is that my problem?

    If so, how could I have excel find those 2 criteria and then provide me the specific # for say Q2'21 renewal in Latin? I was using a vlookup which works, but of course I run the risk of down the road shifting things around and losing some reference.

    Any help would greatly be appreciated!

    This is my formula: =+SUMIF($A$4:$A$9,$A20,INDEX($B$4:$AD$9,0,MATCH($C20,$B$3:$AD$3,0)*MATCH($B20,$B$2:$AD$2,0)))

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Help please? Sumif + index + match not working correctly

    =+sumif($a$4:$a$9,$a20,index($b$4:$ad$9,0,match($c20,$b$3:$ad$3,0)+match($b20,$b$2:$ad$2,0)-1))

  3. #3
    Registered User
    Join Date
    04-15-2021
    Location
    Fall City, WA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Help please? Sumif + index + match not working correctly

    Great thanks! I see 2 differences: the "+" sign and then the -1. What does the -1 do? I assume the + sign is saying to match both those items? Where before I had the * sign in there.
    Thank you!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Help please? Sumif + index + match not working correctly

    If you check the 2 matches:

    The first MATCH returns a value of 2 (matched with Q2'21 in column C)

    The second MATCH returns a value of 7 ("Renewal" in column H)

    Added we get 9 which is column J relative to B)

    But you need column 8 (Q2'21 for "Renewals") so we subtract 1: this because the "MATCH" conditions "overlap"

    This is a common "problem" when doing a double (2 line) MATCH.

  5. #5
    Registered User
    Join Date
    04-15-2021
    Location
    Fall City, WA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Help please? Sumif + index + match not working correctly

    Awesome thank you. That makes sense

    Would I ever use "*" or did I have that wrong overall and need "+" instead? Between 2 match functions?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Help please? Sumif + index + match not working correctly

    Most of the time you will use "+".

    If you are not familiar with "Evaluate formula" (under "Formulas") then this is a good way to understand what happens "under the covers".

  7. #7
    Registered User
    Join Date
    04-15-2021
    Location
    Fall City, WA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Help please? Sumif + index + match not working correctly

    I will explore! Thank you John! Appreciate your help. I just looked up your town on Google. I lived near Woking in Ottershaw for a few years. Beautiful area to live!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Help please? Sumif + index + match not working correctly

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  9. #9
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,256

    Re: Help please? Sumif + index + match not working correctly

    D17 cell formula

    HTML Code: 
    =SUMIF($A$4:$A$9,$A17,OFFSET($B$4:$B$9,,MATCH($C17,$B$3:$AD$3,)+MATCH($B17,$B$2:$AD$2,)-2))
    OR option sumproduct formula

    HTML Code: 
    =SUMPRODUCT((C17=$B$3:$AE$3)*($B$2:$AE$2=B17)*($A$4:$A$9=A17)*$B$4:$AE$9)

  10. #10
    Registered User
    Join Date
    04-15-2021
    Location
    Fall City, WA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Help please? Sumif + index + match not working correctly

    Thank you WK9128. I need to review Offet and also the difference of SumProduct vs sumif. Thank you very much! Both work!!

+ 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. =Index formula not working correctly
    By richie176 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-20-2020, 05:47 AM
  2. =Index formula not working correctly
    By richie176 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-18-2020, 01:38 PM
  3. [SOLVED] small and index formila not working correctly
    By jpbisani in forum Excel General
    Replies: 7
    Last Post: 10-04-2016, 09:37 AM
  4. index & match formula is not working correctly
    By integra in forum Excel General
    Replies: 2
    Last Post: 09-15-2015, 08:30 PM
  5. [SOLVED] Sumif formula across dates not working correctly
    By Vaslo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2014, 06:17 PM
  6. Sumif function not working correctly
    By chouston in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-14-2014, 04:02 PM
  7. Issues With Index/Match Not Working Correctly.
    By mgraesch in forum Excel General
    Replies: 2
    Last Post: 07-31-2009, 10:33 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