Multiple name with data points need to find name and add value of cells next to name.

[speedracer7278]

How do I create function that finds all the same name of salesman thru out entire sheet. Takes all the values of the cell next to the name and add them all up creating a ranking system and have information displayed in a new cell with that salesman name.

11 Bert

12 Mike

5 Larry

2 Mike

7 Larry

1 Bert

2 Larry

1 Mike

Mike = 15

Larry = 14

Bert = 12

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1
2	11	Bert		Bert	12
3	12	Mike		Larry	14
4	5	Larry		Mike	15
5	2	Mike
6	7	Larry
7	1	Bert
8	2	Larry
9	1	Mike
Data

Cell Formulas
Range	Formula
D2:E4	D2	=GROUPBY(B2:B9,A2:A9,SUM,,0)
Dynamic array formulas.

 

[speedracer7278]

How about

Fluff.xlsm
	A	B	C	D	E
1
2	11	Bert		Bert	12
3	12	Mike		Larry	14
4	5	Larry		Mike	15
5	2	Mike
6	7	Larry
7	1	Bert
8	2	Larry
9	1	Mike
Data

Cell Formulas
Range	Formula
D2:E4	D2	=GROUPBY(B2:B9,A2:A9,SUM,,0)
Dynamic array formulas.

Does this work if the name are in many different columns. The same names are in N and Z with values next to them?

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[speedracer7278]

the function worked well. Thanks. How would I sort in the array?

 

[Fluff]

In what way do you want to sort it?

 

[speedracer7278]

Thanks. I want the lowest number from cell E.
I want the lowest ranking on top. It sorts now in alphabetical order.
You guys are great.

 

[Fluff]

Ok, how about

=GROUPBY(B2:B9,A2:A9,SUM,,0,2)

 

[speedracer7278]

Wow how great. Do I owe you some money for this?

 

[Fluff]

No, everything is free of charge.