I can not figure out the correct formula to find the MAX of Total Revenue(E) for Year 1(B) only.

Can someone please help me figure this out?

I've tried =MAX(MATCH(1,(I14=Yrs)(E2:E308),0)) =INDEX(E2:E308,MATCH(1,(E2:E308)(Year_1=C2:C308),0))

And a few others

nyse data

CodePudding user response：

In Office365 and LibreOffice Calc, there is the MAXIFS function. In the example you provided, formula that calculates maximal revenue in "Year 1":

=MAXIFS(E2:E308,C2:C308,"Year 1")

In Excel 2016, the problem can be solved by combining MAX and IF in an array formula:

=MAX(IF(C2:C308="Year 1",E2:E308))

(Don't forget to use CTRL SHIFT Enter to confirm.)