CODEWITHSUNDEEP
excel-formula
DS_London
DS_London

Reputation: 4251

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

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.

enter image description here

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!).

Upvotes: 0

Views: 1231

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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

enter image description here

Another option is SUBTOTAL and OFFSET:

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

enter image description here

Upvotes: 2

Related Questions