Formula - Find the item with the largest sum

[ironbucket1]

Hello!

I want a formula that finds the item with the largest sum, for example:

Ball 2
Plate 3
Knife 1
Fork 3
plate 2
knife 2
ball 6

So in this case the formula would show me *Ball* because the sum is 8 and is the highest sum.
I know I can use sumIF, but I wonder if its possible for a formula to just find the item with the highest Sum without having to refer to something other than highest sum.

Right now I'm using *sumif* on each item and then Max/large on those cells, and that works but if a new item comes on the list I have to do another cell with *sumif*.
I prefer to do this without VBA-coding.

Can someone please help me with this?

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D	E
1	Item	Count		Max Count	Item
2	Ball	2		8	Ball
3	Plate	3
4	Knife	1
5	Fork	3
6	Plate	2
7	Knife	2
8	Ball	6
9
10
Sheet42

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(14,6,SUMIF(A2:A10,A2:A10,B2:B10)/(A2:A10<>""),1)
E2	E2	=INDEX(A2:A10,MATCH(D2,SUMIF(A2:A10,A2:A10,B2:B10),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ironbucket1]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D	E
1	Item	Count		Max Count	Item
2	Ball	2		8	Ball
3	Plate	3
4	Knife	1
5	Fork	3
6	Plate	2
7	Knife	2
8	Ball	6
9
10
Sheet42

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(14,6,SUMIF(A2:A10,A2:A10,B2:B10)/(A2:A10<>""),1)
E2	E2	=INDEX(A2:A10,MATCH(D2,SUMIF(A2:A10,A2:A10,B2:B10),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Sorry for late response! This worked brilliant! Thank you so much May I ask one more thing? These formulas really did the job, but if I wanted to have 2 calculations which one summes up and shows the item (Like this formula you showed me) and then a second one that shows the second biggest sum. On the aggregate formula I could change the last digit to 2, but if 2 items have the same sum the Index-formula shows the same item cause it just matches the first item it sees with that number.

I hope this explonation wasnt to confusing..

// A newbie

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Item	Count		Max Count	Item
2	Ball	2		8	Ball
3	Plate	3		5	Plate
4	Knife	1		3	Knife
5	Fork	3		3	Fork
6	Plate	2
7	Knife	2
8	Ball	6
9
10
Sheet4

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(AGGREGATE(14,6,SUMIF($A$2:$A$10,$A$2:$A$10,$B$2:$B$10)/(COUNTIF($E$1:$E1,$A$2:$A$10)=0)/($A$2:$A$10<>""),1),"")
E2:E8	E2	=IF(D2="","",INDEX($A$2:$A$10,MATCH(D2,SUMIF($A$2:$A$10,$A$2:$A$10,$B$2:$B$10)+COUNTIF(E$1:E1,$A$2:$A$10)/PI(),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

This is not a stand-alone formula, it looks up the values based on excluding the items that are listed above it. But you can drag down the formulas as far as needed.

 

[ironbucket1]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Item	Count		Max Count	Item
2	Ball	2		8	Ball
3	Plate	3		5	Plate
4	Knife	1		3	Knife
5	Fork	3		3	Fork
6	Plate	2
7	Knife	2
8	Ball	6
9
10
Sheet4

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(AGGREGATE(14,6,SUMIF($A$2:$A$10,$A$2:$A$10,$B$2:$B$10)/(COUNTIF($E$1:$E1,$A$2:$A$10)=0)/($A$2:$A$10<>""),1),"")
E2:E8	E2	=IF(D2="","",INDEX($A$2:$A$10,MATCH(D2,SUMIF($A$2:$A$10,$A$2:$A$10,$B$2:$B$10)+COUNTIF(E$1:E1,$A$2:$A$10)/PI(),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

This is not a stand-alone formula, it looks up the values based on excluding the items that are listed above it. But you can drag down the formulas as far as needed.

I dont know how many times i tried to get this to work and you did it in like 5 min, you're a god my man, thank you!

 

[Eric W]

Long practice! Glad I could help! ?

 

[jlsayan]

Hi @Eric W wanted to ask if this formula could add another criteria to sum the values considering if another column is empty or full?

 

[Eric W]

Welcome to the MrExcel forum!

Something like this maybe?

Book1
	A	B	C	D	E
1	Item	Count	Use?	Max Count	Item
2	Ball	2	X	5	Plate
3	Plate	3	X	3	Fork
4	Knife	1		2	Ball
5	Fork	3	X
6	Plate	2	X
7	Knife	2
8	Ball	6
Sheet6

Cell Formulas
Range	Formula
D2:E4	D2	=LET(a,UNIQUE(FILTER(A2:A8,C2:C8<>"")),SORT(CHOOSE({1,2},SUMIFS(B2:B8,A2:A8,a,C2:C8,"<>"),a),,-1))
Dynamic array formulas.