Find Largest Sum and Item using an Array Formula

[John_F]

I'm trying to determine the largest amount without using a sum column (or helper column). Is there a way to do this with an array formula?

Excel 2010

	A	B	C	D	E
ITEM 1
ITEM 2
ITEM 3
Item	Amount
Largest

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]12
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]6
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]23
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]24
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]18
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]24
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[sheetspread]

Excel 2010
	A	B	C	D	E
1	ITEM 1	12	2	23	6
2	ITEM 2	23	19	9	24
3	ITEM 3	18	10	9	24
4
5		Item	Amount
6	Largest	ITEM 2	75
Sheet5

Cell Formulas
Range	Formula
B6	=INDEX($A$1:$A$3,MATCH(MAX(MMULT($B$1:$E$3,TRANSPOSE({1,1,1,1}))),MMULT($B$1:$E$3,TRANSPOSE({1,1,1,1})),0))
C6	=MAX(MMULT($B$1:$E$3,TRANSPOSE({1,1,1,1})))

There's better, but this will work if no sums repeat

 

[sheetspread]

Or:

Excel 2010
	A	B	C	D	E
1	ITEM 1	12	2	23	6
2	ITEM 2	23	19	9	24
3	ITEM 3	18	10	9	24
4
5		Item	Amount
6	Largest	ITEM 2	75
Sheet5

Cell Formulas
Range	Formula
B6	=INDEX($A$1:$A$3,MATCH(MAX(MMULT($B$1:$E$3,{1;1;1;1})),MMULT($B$1:$E$3,{1;1;1;1}),0))
C6	=MAX(MMULT($B$1:$E$3,{1;1;1;1}))

 

[Aladin Akyurek]

I think we need to be a bit more elaborate...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]ITEM 1[/td][td]

12​

[/td][td]

2​

[/td][td]

23​

[/td][td]

6​

[/td][/tr]

[tr][td]

2​

[/td][td]ITEM 2[/td][td]

23​

[/td][td]

19​

[/td][td]

9​

[/td][td]

24​

[/td][/tr]

[tr][td]

3​

[/td][td]ITEM 3[/td][td]

18​

[/td][td]

10​

[/td][td]

9​

[/td][td]

24​

[/td][/tr]

[tr][td]

4​

[/td][td]ITEM 4[/td][td]

20​

[/td][td]

19​

[/td][td]

14​

[/td][td]

22​

[/td][/tr]

[tr][td]

5​

[/td][td][/td][td]Largest
rowwise
sum[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td]

75​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td]Item[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td]ITEM 2[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td]ITEM 4[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

B6, control+shift+enter (cse), not just enter:

=MAX(MMULT(IF(ISNUMBER($B$1:$E$4),$B$1:$E$4,0),
    TRANSPOSE(COLUMN($B$1:$E$4)^0)))

B8, cse and copy down:

=IFERROR(INDEX($A$1:$A$4,
    SMALL(IF(MMULT(IF(ISNUMBER($B$1:$E$4),$B$1:$E$4,0),
    TRANSPOSE(COLUMN($B$1:$E$4)^0))=$B$6,ROW($A$1:$A$4)-ROW($A$1)+1),
    ROWS($B$8:B8))),"")

 

[John_F]

Thanks. I have been struggling with this. Never used the MMULT function before. I will have to look into it further.

 

[Aladin Akyurek]

Thanks. I have been struggling with this. Never used the MMULT function before. I will have to look into it further.

You are welcome.