Help with SUMIFS dynamic sum range

[gohawks222]

I've found various topics about this, but I just can't seem to get mine to work. Here is my formula:

=SUMIFS(INDEX('[FY16 ForecastMaster.xlsm]Orders'!$B:$NW, 0, MATCH(DateMatrix!$F$1, '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$2,0)), '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$B$357, Sheet1!B2)

I'm getting a #VALUE error, and I have a feeling it's because of the dates. The sum range (column label are week-end dates) should change based on the match from the date matrix. The week-end date in F1 for example is derived from a vlookup, which is derived from TODAY(), so there may be some volatility there. The column labels are in the range B2:B357, and it should just change to match the F1 value.

Thank you.

 

[Jonmo1]

It's because the sum range and criteria ranges are of different dimentions.
The sum range is an entire column, but the criteria range is only rows 2 to 357

Try changing
FY16 ForecastMaster.xlsm]Orders'!$B:$NW
to
FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$357

 

[Jonmo1]

Also, since it's only 1 criteria, it should be just sumif, not sumifS

=SUMIF('[FY16 ForecastMaster.xlsm]Orders'!$B$2:$B$357, Sheet1!B2, INDEX('[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$357, 0, MATCH(DateMatrix!$F$1, '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$2,0)))

 

[gohawks222]

Awesome, worked perfectly. I had a feeling I was screwing up the ranges but I couldn't figure it out. Also, I've gotten in the habit of using SUMIFS all the time because I like that the sum range comes first for some reason . Thanks for the help!