Find the row name with the highest sum of numbers-CodePudding

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!

CodePudding user response：

** 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),

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