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!