Get max value

[Sasquatch2014]

i'm trying to get a max value based on 2 criteria.

call data sheet is the raw data source

i press Shift, ctrl, enter and get error


=MAX(IF('call data'!B2:B14=F2,IF('call data'!C2:C14=G2,'call data'!D2:D14)))

[XLent]

I would revert to

Please Login or Register  to view this content.

the above simply divides the values by the boolean results, if any 1 boolean returns FALSE the act of #/0 will lead to error, and that value will be ignored -- the use of 15 (LARGE) and 6 (ignore errors) means you will get the largest valid result
(add an outer IFERROR if you need to account for possibility of no valid results)

[Sasquatch2014]

ty. what is the 14 and 6 in the beginning for?

[XLent]

sorry, typo in my prior post, 14 is the function call (LARGE) and 6 is the option which, in this instance, tells AGGREGATE to ignore errors, the final parameter of 1 provides the "k" value for use with LARGE, i.e. 1 (largest)

[Sasquatch2014]

thanks for the explanation. the formula worked