I need to retrieve the max value from a variable number of rows and columns where the row header equals a certain value, 300 in the above example would return a max of 15.
I'm trying to create a formula where the row header (e.g. 300) would be a variable so the max could be returned for row headers 300 or 600 and so on.
How do get a range or array of all the rows with 300 as the header so I can use the Max function on that range?
Best Answer
Put your 300 in F1, then in D1 put the array formula:
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.
Because of the column A criteria, the 101 on row 14 is ignored.