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!

=+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))

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!

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.

Awesome thank you. That makes sense

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

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".

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!

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.

D17 cell formula

HTML Code: 

OR option sumproduct formula

HTML Code: 

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