Return Multiple Values in a Column after Matching Highest Number

[gikaar]

I'm having an issue trying to figure out the formula for what I'm trying to accomplish. Here is what I'm trying to do:


Word1 1 1
Word1 2 1
Word1 3 2
Word1 4 3
Name1 1 0
Name1 2 1
Name1 3 1
Word2 1 0
Word2 2 0
Word2 3 1
Word2 4 2
Name2 1 1
Name2 2 2
Name3 3 3

I'm trying to "pull" the max number in column 3 (a) that matches the max number of column2 and column1, and then add that number to the next highest number (b) for the next match, and so on.

Word1 4 3 = a
Name1 3 1 = b
Word2 4 2 = c
Name2 3 3 = d

a+b+c+d=x

Any help would be appreciated.

Thank you.

 

[gikaar]

More clarification:

In my example, the final formula of a+b+c+d=x would be (3+1+2+3)=9. I just don't know the formula to get there.

 

[Eric W]

Welcome to the MrExcel forum!

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1					Unique Names					Excel 365
2	Word1	1	1		Word1	4	3	9		9
3	Word1	2	1		Name1	3	1
4	Word1	3	2		Word2	4	2
5	Word1	4	3		Name2	3	3
6	Name1	1	0
7	Name1	2	1
8	Name1	3	1
9	Word2	1	0
10	Word2	2	0
11	Word2	3	1
12	Word2	4	2
13	Name2	1	1
14	Name2	2	2
15	Name2	3	3
Sheet5

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/(COUNTIF($E$1:$E1,$A$2:$A$15)=0),1)),"")
F2:F9	F2	=IFERROR(AGGREGATE(14,6,$B$2:$B$15/($A$2:$A$15=E2),1),"")
G2:G9	G2	=IFERROR(AGGREGATE(14,6,$C$2:$C$15/($A$2:$A$15=E2)/($B$2:$B$15=F2),1),"")
H2	H2	=SUM(G:G)
J2	J2	=LET(u,UNIQUE($A$2:$A$15),m,MAXIFS($B$2:$B$15,$A$2:$A$15,u),SUM(MAXIFS($C$2:$C$15,$A$2:$A$15,u,$B$2:$B$15,m)))

With Excel 2016, I had to use a series of formulas to get it to work. The E column creates a list of unique names, the F column finds the max value for each name from column B, the G column finds the corresponding value from column C, and the H column sums them up. The J2 formula shows how Excel 365 can do it all in a single cell. Note that both versions could have issues if the values in column B can be duplicated. Let me know if that can happen, and if so, what you want to do about it.

Also, your example showed a Name3 on the end - I assumed it was a typo for Name2.

Hope this helps!

 

[gikaar]

Thank you very much! That's exactly what I need!

And yes, the Name3 on the end was a typo.

 

[Peter_SSs]

Perhaps your sample data is not representative of your actual data, otherwise what about this?

23 09 16.xlsm
	A	B	C	D	E
1
2	Word1	1	1		9
3	Word1	2	1
4	Word1	3	2
5	Word1	4	3
6	Name1	1	0
7	Name1	2	1
8	Name1	3	1
9	Word2	1	0
10	Word2	2	0
11	Word2	3	1
12	Word2	4	2
13	Name2	1	1
14	Name2	2	2
15	Name2	3	3
16
Sum

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(A2:A15<>A3:A16),C2:C15)