How can I get the SUM of the MAX value of each ROW in a single cell formula?

Question

I have the following array of data in cells B2:E10. I want to find the sum of the maximum values in each row of the array ... BUT in a single cell formula (to get the result in cell G12), rather than using the intermediate steps that I have shown in the sample.

I'm using the latest Excel, so have access to array formulae, LET() etc, but I am struggling to achieve my goal. The problem is that MAX() always seems to work on the whole grid, so a formula like this doesn't work:

=SUM(MAX(INDEX($B$2:$E$10,SEQUENCE(9),0)))

It may be that MAX doesn't understand array formulae, so I tried various combinations of SORT() for example but I failed to generate an array of individually-sorted columns.

This problem isn't blocking me, but now I have asked myself the question I am interested to know if there is a solution (which may be very simple, and I just can't see it!).

Answer 1

MAX accepts arrays as entry so it will see all the returns before finding the max.

We can use MAXIFS and OFFSET to create an array of outputs line by line:

 =SUM(MAXIFS(OFFSET(B2:E2,SEQUENCE(9,,0),0,1,4),OFFSET(B2:E2,SEQUENCE(9,,0),0,1,4),"<>"))

---

Another option is SUBTOTAL and OFFSET:

=SUM(SUBTOTAL(4,OFFSET(B2:E2,SEQUENCE(9,,0),0,1,4)))