SUMIFS Using index and match to find the row range

[Peter Davison]

SUMIFS(INDEX('[MasterData.xlsm]Unit Sales'!$AQ:$MMZ,MATCH($C51,'[MasterData.xlsm]Unit Sales'!$AQ:$AQ,0))

I've been doing SUMIFS and index and match for ages with no trouble, mostly using the match on a column, but this time I want the sum range to find the row to go with the columns AQ to MMZ.
I've tried the above formula (this part for the sum range) to get it to return the row reference which should be AQ6 to MMZ6. I did an F9 on the match part and that does return the 6, but then the first part returns a REF.
I can't work out what I am doing wrong?
Any help would be great.

 

[jasonb75]

You've only specified a row in the index range, the column identifier is missing. As you want it to look at all columns in the range you need to use an identifier of 0 (see below)

SUMIFS(INDEX('[MasterData.xlsm]Unit Sales'!$AQ:$MMZ,MATCH($C51,'[MasterData.xlsm]Unit Sales'!$AQ:$AQ,0),0)

 

[Peter Davison]

Thankyou so much, so simple yet so effective.