The article below explains both approaches.
See below for more information.
MAXIFS solution
TheMAXIFS functioncan get the maximum value in a range based on multiple criteria.
Notice we need toconcatenatethe dates tological operators, as required by the MAXIFS function.
Using EDATE is therefore simpler and more robust.
In this version, we use theTEXT functionto convert thedatestotext stringsin the format “mmyy”.
This array is then multiplied by the values inamount.
For a side-by-side comparison of formulas vs. pivot tables, see this video:Why pivot tables.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
TEXT Function
The Excel TEXT function returns a number formatted as text.
in a text string with the number format of your choice.
Related videos
How to calculate maximum and minimum values
