How to Count Second Largest Value for Groups

You can count the maximum value using the Max function, or the second largest value using the LARGE function in a range. However, you will need to change the formula when counting the second-largest value for a group.

Example: You work with the data below with the groups in column A, regions in column B, and salaries in column C.

Formula 1: To count the second-largest value for group A

To find the second-largest value in Group A, we will use the following formula:

=LARGE(IF(A2:A12="Group A", C2:C12),2)

  • (A2:A12="Group A"): returns "true" if Group A, and "false" if not Group A;
  • IF(A2:A12="Group A", C2:C12): if Group A in column A, values in column C, otherwise, returns zero;
  • LARGE(IF(A2:A12="Group A", C2:C12),2) is to find the second-largest value from the new value list.

Formula 2: To count the second-largest value for group A

You can also modify the formula as the one below

=LARGE((A2:A12="Group A") * (C2:C12),2)

  • (A2:A12="Group A"): returns "true" if Group A, and "false" if not Group A;
  • (A2:A12="Group A") * (C2:C12): if Group A in column A, values in column C, otherwise, returns zero;
  • LARGE((A2:A12="Group A")*(C2:C12),2): To find the second-largest value from the new list.
  • Please note that the result will return an error if you have only one value in the group (e.g., North region).

Leave a Reply