Find the row name with the highest sum of numbers

user_12893747 0
Viewed 29

In the below spreadsheet, I am trying to find the item with the highest total sales across various hours.

I would like to easily extract the highest and lowest selling item names as well as their corresponding total sales. I need to be able to do this without creating a helper column as I cannot edit the table.

I know there's probably an easy way to do this but for the life of me I cannot figure it out!

Link to Sheet

google-sheets-formula google-sheets max sum
2 Answers

B12:

=INDEX(SORT(SPLIT(FLATTEN(B2:G10&"×"&A2:A10&"×"&B1:G1), "×"), 1, 0), 1, 2)

B13:

=INDEX(SORT(SPLIT(FLATTEN(B2:G10&"×"&A2:A10&"×"&B1:G1), "×"), 1, 0), 1, 1)

B14:

=INDEX(SORT(SPLIT(FLATTEN(B2:G10&"×"&A2:A10&"×"&B1:G1), "×"), 1, 1), 1, 2)

B15:

=INDEX(SORT(SPLIT(FLATTEN(B2:G10&"×"&A2:A10&"×"&B1:G1), "×"), 1, 1), 1, 1)

enter image description here

user_56326290

** edit: use player0's for the overall min and max.

Check this demo sheet

To get the highest or lowest by time slot (put this in I5),

=ARRAYFORMULA(
  IFERROR(
   VLOOKUP(
    TRANSPOSE(B1:G1),
    SORT(
     SPLIT(
      FLATTEN(B1:G1&"|"&A2:A&"|"&B2:G),
      "|"),
     3,FALSE),
    {1,2,3},FALSE)))

Then to align the lowest per timeslot with that, put this in M5

=ARRAYFORMULA(
  IF(ISBLANK(I5:I),,
   IFERROR(
    VLOOKUP(
     I5:I,
     SORT(
      SPLIT(
       FLATTEN(B1:G1&"|"&A2:A&"|"&B2:G),
       "|"),
      3,FALSE),
     {2,3},FALSE))))

If you also wanted conditional formatting for the daily high and low, use a range of B2:G with

=AND(LEN(B2),MIN($B2:$G2)=B2)

If you really want to have these formulas below the table, change

FLATTEN(B1:G1&"|"&A2:A&"|"&B2:G),

to

FLATTEN(B1:G1&"|"&A2:A10&"|"&B2:G10),
user_122540980
Related