Where sales (D5:D15), dates (B5:B15) and values (C5:C15) are named ranges. Note: we are assuming date in G4 is a “first of month” date. The second criteria checks if dates are less than or equal to the last of the month, calculated with the EOMONTH function: When both criteria return TRUE, the date is in the given month, and MAXIFS returns the max of value of dates that meet criteria.
Alternative formulas
If your version of Excel doesn’t include the MAXIFS function, there are other options. One option is a simple array formula based on the MAX and IF functions: Note: This is an array formula and must be entered with with control + shift + enter. Another option is to use the AGGREGATE function like this: Both options above use a simpler method of testing dates based on the TEXT function which uses a number format to extract the month and year from a date. The TEXT function can’t be used like this inside the MAXIFS function, because MAXIFS requires a range. These alternative approaches are explained in more detail here.
The max client
To retrieve the client associated with the max value in a given month, the the formula in G6 is: This is an array formula and must be entered with with control + shift + enter. The idea here is that we already know the max value in a month (G5) and we can use that value as a “key” to lookup the client. Note we must repeat the logic used to isolate values in the given month, in order to avoid a false match in a different month. Like the alternatives mentioned above, this formulas also uses a simplified test for dates based on the TEXT function. Read more about INDEX and MATCH with multiple criteria here. For an all-in-one formula, replace G5 with one of the formula options explained above for determining the max value in a given month.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
