Sum in a index/match formula

Question

Could you help me solve the following?

I want to return the total sum, not the first match that it finds. My first preference is have indexing and matching with the sum total, if this CAN'T be done then an if statement. I have include 'sheet1!' in the formula as it will be over 2 work sheets. Below is an example of the formula, the data will be over 4000 lines.

I can't post a image as I'm new but the array formula is

=INDEX(Sheet1!$G$4:$I$10,MATCH(A2&B2,Sheet1!$G$4:$G$102&Sheet1!$H$4:$H$102,0),3)

In the data that it is matching it is return the first result of many, I what the total amount.

Answer 1

Try to use following formula:

=SUMIFS(Sheet1!$I$4:$I$102,Sheet1!$G$4:$G$102,A2,Sheet1!$H$4:$H$102,B2)

or

=SUMPRODUCT((Sheet1!$G$4:$G$102=A2)*(Sheet1!$H$4:$H$102=B2)*(Sheet1!$I$4:$I$102))