Max(if); Finding max number based on criteria

Hi All,

I have a data-set that has weekly historical running total data. I have to do an analysis that ties in said weekly data with other data that was recorded monthly. In order to be able to do this I would have to pull data for the last week of each month, however, I am having trouble creating a formula that would do that.

I’ve attached a rudimentary example of the data set to this post.

The data-set has a Location, Period, Year, Week, and Data columns.

Originally I originally tried finding the “max” week number (which would be either 4 or 5 depending on period) using a =max(if) (formula found in column F) comparing the location, period, and year. But that formula would always spit out 5 since it looked at the entire data-set rather the specific location, period, and year.


I’m running office 16 which doesn’t have the maxifs capability yet.



Any help would be greatly appreciated!

polishfc, one comment. Wrong way to use whole column range for array formula. Use range such a1:a28 or A1:100 even a1:10000 but not A:A.

also, non array formula could be used =AGGREGATE(16;6;D2:D28/(B2:B28=B2)/(A2:A28=A6);1)

Thanks for the input everyone, appreciate the help. I ended up using =MAX(INDEX()) formula that