Selecting the max with a condition in excel

Question

I have a range in excel with dates and values

EDIT:image added

I want to select the MAX value from the HIGH column for each YEAR (2009 would return 404, 2008 would return 390)

I've done something similar but it's using SUMIF, not MAX.

Any excel people in here that can help me out?

Answer 1

What you're looking for is array formulas (tip: CTRL+SHIFT+ENTER)

here's something

Answer 2

The equivalent of SUMIF in Excel is something like this:

{=MAX(IF(CategoryColumn="High",ValueColumn,"")}

Where

• CategoryColumn is the column containing your categories (e.g., "Low", "Med", "High")
• ValueColumn is the column containing the data you want to get the max of

NOTE: This is an array formula so you must press Ctrl-Shift-Enter when entering this formula instead of just Enter. Also, don't actually type in the braces {}. The braces show up automatically to indicate that you've entered an array formula.

NOTE 2: You can actually name a range of data. For example, select the range A1:A20. Right-Click and select "Name a Range..." So, for this example, you can select your category column and name it CategoryColumn. Same with ValueColumn.

Array formulas allow you to do IF statements and other functions on a whole range of data instead of just a single value.

This example checks if the value in the "category column" is "High". If it is, it puts the neighboring "value" into the MAX function, otherwise it puts a blank. This gives you the maximum of all the "High" values.

P.S. I don't think the img tag works on this site...can't see your image.