CODEWITHSUNDEEP
excel
DingHou Wong
DingHou Wong

Reputation: 1

2D - Finding largest sum of different combination in Excel

I have this monthly sales by item and want to find which combination gives the largest sum. Example,

           Feb 2020  Mar 2020 Apr 2020 May 2020
Shoes          2         2        1        0
Phone          2         3        10       1
Computer       5         7        7       10

The answer is 5+7+10+10=32. I need to find the largest sum for over 50 items. Any help very much appreciated.

Upvotes: 0

Views: 142

Answers (4)

DingHou Wong
DingHou Wong

Reputation: 1

Thanks to

Another option for columns:

=SUM(SUBTOTAL(4,OFFSET(INDEX(B2:E4,1,1),,COLUMN(B2:E4)-MIN(COLUMN(B2:E4)),ROWS(B2:E4)))) for rows:

=SUM(SUBTOTAL(4,OFFSET(INDEX(B2:E4,1,1),ROW(B2:E4)-MIN(ROW(B2:E4)),,,COLUMNS(B2:E4)))) These are array formulas so after editing confirm them by pressing ctrl + shift + enter

enter image description here

Upvotes: 0

basic
basic

Reputation: 11968

Another option for columns:

=SUM(SUBTOTAL(4,OFFSET(INDEX(B2:E4,1,1),,COLUMN(B2:E4)-MIN(COLUMN(B2:E4)),ROWS(B2:E4))))

for rows:

=SUM(SUBTOTAL(4,OFFSET(INDEX(B2:E4,1,1),ROW(B2:E4)-MIN(ROW(B2:E4)),,,COLUMNS(B2:E4))))

These are array formulas so after editing confirm them by pressing ctrl + shift + enter

enter image description here

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36880

One tricky solution is as below.

=SUM(MAX(B2:B4),MAX(C2:C4),MAX(D2:D4),MAX(E2:E4))

enter image description here

Upvotes: 1

wasif
wasif

Reputation: 15488

You can use =SUM(B2:B5) and drag it using the bottom-right edge to the other cells in column to apply the same formula.

Upvotes: 0

Related Questions