Identify the higest value and sum across groups

Your layout is not great, but try this in E2:

=IF(A2=A3,"",SUM(C1:C2))

and this in F2:

=IF(E2="","",LOOKUP(MAX(C1:C2),C1:C2,D1:D2))

Then you failed to mention a really rather important thing! Back to the drawing board ...

Please attach a file that is representative of your real data and layout.

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

In F2:

=IF(E2="","",INDEX(D:D,MATCH(1,(A:A=A2)*(C:C=MAXIFS(C:C,A:A,A2)),0)))

confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Drag copy the formula in F2 down again, then it will work.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	Name	Subject	Marks	Category	Total Marks	Highest Scored Category	TEST
2	Alex	Physics	35	PHY			85
3	Alex	Physics Practical	45	PHY PRA			85
4	Alex	English	85	ENG	165	ENG	85
5	Bob	Physics	45	PHY			45
6	Bob	Physics Practical	25	PHY PRA	70	PHY	45
7	Kim	Math	35	MTH			78
8	Kim	Chemistry	56	CHEM			78
9	Kim	Chemistry Practical	45	CHEM PRA			78
10	Kim	English	78	ENG	214	ENG	78

Sheet: Sheet1

I am using YOUR file. Have you done what I suggested? On YOUR file, it puts the problem right. I am on the same Windows platform and Excel version.

Do you have calculation set to manual by any chance?

Glad to have helped, anyway.