Using Match to return the highest value for a coincidence

[FrankParker]

Hi, this is my first post here!

The reason i suscribed here is because I was looking forward to improve a formula to match my needs. Let me tell you about it a little:

I have this data set:



When you put an X on column E it sums always the value that is displayed on there, no matter what letter appears on column C. When you put an O, if the letter that appears on column C doesn't have another O then it sums, but if the letter on column A already has another O it doesn't. The letter on column A doesn't always have the same associated value on column B, what I am looking for is that if any letter on column C has two values, when I put a circle in column C, it only sums the highest value, but if the O it's on the lower value only then sums that value.
The idea is that it sums always that I put an X on the far right column, and it only sums once when i put an O.

I used this formula:
SUMIF(E2:E179;"X";$D$2:$D$22)+(SUM(IF(FREQUENCY(IF(ISNUMBER($D$2:$D$22);IF(E2:E22="O"; SI(ISNUMBER(SEARCH("O";E2:E22));MATCH($C$2:$C$22;$C$2:$C$22;0))));ROW($C$2:$C$22)-ROW($C$2)+1);$D$2:$D$22)))

It works mostly as its intended purpose except that if an item in the category has more than one value it sums the first value that appears, but as I told you before, I am trying to make it sum the highest value for each category only when. I could sort the table for the categories, but for later search it would not be optimal.Any idea how I could fix the formula?


Here you have a file to experiment with if you want
Box


Thanks!

 

[FrankParker]

Oh no, you are correct, I had put it wrong The circle should have gone on the center! Let me fix it:

id	Item	Valor	Columna3
0094	V	0	X			Correct result should be
0095	K	13135	x			181544
0096	W	8880	x
0100	T	23115	X			Obtained:
0102	O	24216	x			First Formula	167534
0103	AQ	11435	x			Second Formula	187314
0105	Y	12956				Third Formula	180490
0107	Y	14010	o
0108	Y	14011
0109	B	650	o
0110	B	650	o
0116	P	9231	o
0117	P	15000
0118	P	14059
0119	Q	1964
0120	Q	3000	x
0121	N	35825	x
0129	N	8047	x
0131	C	0
0133	C	0
0134	AE	30000	x

 

[Eric W]

Try:

=SUM(IF(D2:D22="o",IF(COUNTIFS(B2:B22,B2:B22,D2:D22,"o")=1,C2:C22,MAXIFS(C2:C22,B2:B22,B2:B22,D2:D22,"o")/COUNTIFS(B2:B22,B2:B22,D2:D22,"o"))))+SUMIF(D2:D22,"x",C2:C22)

This is an array formula. Depending on whether your version of Excel 365 has the latest calculation engine, you might need to confirm the formula by pressing Control+Shift+Enter when you enter the formula in the formula bar.

 

[FrankParker]

I think you nailed it this time! Thank you very much!

 

[Eric W]

Glad to help!