You can calculate the maximum value in a dataset with the Max function. When counting the maximum value within a group, you can get them using the pivot tables or using the formulas below.
Example: In the following dataset, groups are in column A, regions are in column B and salaries are in column C.
Formula 1: To count the maximum value in group A
=MAX(IF(A2:A12="Group A", C2:C12))
=MAX(IF(A2:A12=A2, C2:C12))
IF(A2:A12="Group A", C2:C12): to create a new array with Group A's data, and other groups will become "false".
The group name can be replaced with the reference cell, e.g., A2 includes the value of "Group A" in this example.
The result returns $100,835.00.
Formula 2: To count the maximum value in group B
You can also use the MAXIFS function if you have Excel 2019, Excel 365, or a later version. However, the result will return an error if you have the earlier version.
=MAXIFS(C2:C12, A2:A12, "Group B")
(Excel 2019, Excel 365, or a later version)
The result returns $86,632.00.
Formula 3: To count the maximum value in group C
=MAX(IF(A2:A12="Group C",C2:C12))
=MAXIFS(C2:C12, A2:A12, "Group C") – version 2019 or after
IF(A2:A12="Group C", C2:C12): to create a new array with Group C's data, and other groups will become "false".
The group name can be replaced with the reference cell, e.g., A9 includes the value of "Group C" in this example.
The result returns $70,256.00.
