Sum results from index match array formula

[CONORF]

Hello. I am hoping someone can help me. I have spent many hours trying to get a solution. I have tried sum product, sumifs, etc with no luck. I think my problem might be the way I am ordering the data but I'm not sure. The index match array works wonderfully to look up this weird situation however I cannot figure out a way to get it to sum all of the results as I have multiple locations that belong to the same group that I want to sum. I am looking up on a separate sheet the two rows which are location number and type and the column is the month. The lookup to type is not on the same row as the location number. Below is the formula I am using. Any help would be greatly appreciated.

=INDEX('Sheet1'!$B$4:$f$50,MATCH($A8&$D9,'Sheet1!$A$4:$A$50&'Sheet1'!$C$4:$C$50,0),MATCH(Z$3,'Sheet1'!$B$4:$f$4,0))



'Sheet1'!$B$4:$f$50: This is the data set that I want it to find the results and sum
$A8: this is the location number
$D9: this is the data type (revenue per business day)
'Sheet1!$A$4:$A$50: houses the location number on the other sheet
'Sheet1'!$C$4:$C$50: houses the data type description on the other sheet
Z$3: the month I am looking for (column)
'Sheet1'!$B$4:$f$4: houses the month I am looking for on the other sheet

 

[Aladin Akyurek]

Does this work for you?

=SUMIFS(INDEX('Sheet1'!$B$4:$F$50,0,MATCH(Z$3,'Sheet1'!$B$4:$F$4,0)),'Sheet1!$A$4:$A$50,$A8,'Sheet1'!$C$4:$C$50,$D9)

 

[CONORF]

Does this work for you?

=SUMIFS(INDEX('Sheet1'!$B$4:$F$50,0,MATCH(Z$3,'Sheet1'!$B$4:$F$4,0)),'Sheet1!$A$4:$A$50,$A8,'Sheet1'!$C$4:$C$50,$D9)

Thank you so much! you are a lifesaver. This worked perfectly.

 

[Aladin Akyurek]

Thank you so much! you are a lifesaver. This worked perfectly.

You are welcome.