enter image description here

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?

up vote 2 down vote accepted

Put your 300 in F1, then in D1 put the array formula:

=MAX(IF(A1:A100=F1,B1:C100))

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.

enter image description here

Because of the column A criteria, the 101 on row 14 is ignored.

If you have Office 365 Excel, the normal formula:

=MAX(MAXIFS(B:B,A:A,300),MAXIFS(C:C,A:A,300))

You can replace the both 300 with a cell address. Since this is not an array formula it will allow full column references without detriment.

  • +1, I should have mentioned it's Office 2010 at my workplace but this is handy for future reference – Absinthe Aug 30 '17 at 13:09

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.