SUM, INDEX, MATCH with 2 criteria in rows and columns

[Bethany Sachtleben]

Hi,

I'm trying to return the sum of all the amounts under the Source "INAP Ubersmith" for the month of January. As you can see in this screenshot, The source criteria is in a column and the month criteria is in a row. My current formula returns all amounts for the month of January but it does not single out the source I want.

Here is the current formula: =SUM(INDEX(Sheet1!G4:H163,0,MATCH(CC2,Sheet1!G3:H3,0),1))

This formula is in a different tab in the same workbook, so "sheet 1" is the below data and "CC2" is simply a cell with the word "Jan".

Would really appreciate any help!

 

[jasonb75]

It looks like you need to use SUMIFS instead of SUM

=SUMIFS(INDEX(Sheet1!$G$4:$H$163,0,MATCH(CC2,Sheet1!$G$3:$H$3,0)),Sheet1!$C$4:$C$163,"INAP Ubersmith")

 

[Bethany Sachtleben]

It looks like you need to use SUMIFS instead of SUM

=SUMIFS(INDEX(Sheet1!$G$4:$H$163,0,MATCH(CC2,Sheet1!$G$3:$H$3,0)),Sheet1!$C$4:$C$163,"INAP Ubersmith")

Thanks a ton! It worked!

 

[Bethany Sachtleben]

It looks like you need to use SUMIFS instead of SUM

=SUMIFS(INDEX(Sheet1!$G$4:$H$163,0,MATCH(CC2,Sheet1!$G$3:$H$3,0)),Sheet1!$C$4:$C$163,"INAP Ubersmith")

Follow up question: I'm trying to add an additional criteria within the match formula so that it also looks at the year and sums values from 2021. How would I imbed that within the formula?

 

[jasonb75]

You would need a totally different formula if you have more than 1 criteria for the columns as well.

=SUMPRODUCT(Sheet1!$G$4:$H$163,(Sheet1!$C$4:$C$163="INAP Ubersmith")*(Sheet1!$G$2:$H$2=2021))

There are a number of ways but this would be the most reliable.