using a formula, I'm looking to get the max of column C for each unique value in column A then I'd like to also return the corresponding value from column B to the max of column C. the output should be a summary of the data as in the second screen capture. I've tried pivot tables, conditional formatting and the help in this link with no luck Get the maximum values of column B per each distinct value of column A
Is this possible with formulas?
So for the Max number use the AGGREGATE() Function:
=AGGREGATE(14,6,$C$2:INDEX(C:C,MATCH(1E+99,C:C))/($A$2:INDEX(A:A,MATCH(1E+99,C:C))=I2),1)
Then to find the Capacity a simple SUMIF():
=SUMIFS(B:B,A:A,I2,C:C,K2)
The AGGREGATE() Function was introduced in Excel 2010.
For other versions, the less robust Array Formula MAX(IF()) will work in place of the AGGREGATE:
=MAX(IF($A$2:INDEX(A:A,MATCH(1E+99,C:C))=I2,$C$2:INDEX(C:C,MATCH(1E+99,C:C))))
Being an Array Formula it must be confirmed with Ctrl-Shift-Enter when exiting Edit mode. When done correctly Excel will automatically put {} around the formula to denote an array formula.
Another, If you have the latest Office 365 or using the online app the following non CSE MAXIFS() will work also:
=MAXIFS($C$2:INDEX(C:C,MATCH(1E+99,C:C)),$A$2:INDEX(A:A,MATCH(1E+99,C:C)),I2)
These two are not as robust as the AGGREGATE() in that they will break if there are errors in the data. AGGREGATE() will ignore them.
Here's another option using Array formula, in a Table. I didn't do this starting at A1 in my sheet, so the image has my cell/row IDs to assist!
Cell M17: =IFNA(INDEX([Host], MATCH(0, COUNTIF($M$16:M16, [Host]), 0)),"Unknown") +Ctrl+Shift+Enter
Cell N17: =MAX(IF([Host]=[@UniqueHost],[Duration])) +Ctrl+Shift+Enter
Cell O17: =IFNA(INDEX([Capacity],MATCH([@UniqueHost]&[@MaxDuration],[Host]&[Duration],0)),0) +Ctrl+Shift+Enter
