# Help please? Sumif + index + match not working correctly

1. ## 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!

Thank you!

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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!!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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