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!

 

[Eric W]

Welcome to the MrExcel forums!

Try:

Book1
	A	B	C	D	E	F	G	H	I
1		id
2		94	V	0	X		Obtained	X
3		95	K	13135				OK
4		96	W	8880
5		100	T	23115	X
6		102	O	24216
7		103	AQ	11435		83099
8		105	Y	12956	O
9		107	Y	14010	O
10		108	Y	14010	O
11		109	B	650
12		110	B	650
13		116	P	9231
14		117	P	14059
15		118	P	14059
16		119	Q	1964	O
17		120	Q	1964	O
18		121	N	35825
19		129	N	8047
20		131	C	0	O
21		133	C	0	O
22		134	AE	30000	X
Sheet2

Cell Formulas
Range	Formula
F7	F7	=SUMIF(E2:E179,"X",D2:D179)+SUMPRODUCT(--(E2:E179="O"),--(MATCH(D2:D179,D2:D179,0)=ROW(D2:D179)-ROW(D2)+1),MAXIFS(D2:D179,C2:C179,C2:C179,E2:E179,"O"))

See if this does what you want. You'll have to convert it to your local settings (commas --> semicolons, and it appears you started with Spanish terms (IF --> SI).

 

[Eric W]

Oops!

I referred to the wrong column in the formula, it should be:

Book1
	A	B	C	D	E	F	G	H
1		id
2		94	V	0	X		Obtained	X
3		95	K	13135				OK
4		96	W	8880
5		100	T	23115	X
6		102	O	24216
7		103	AQ	11435		69089
8		105	Y	12956	O
9		107	Y	14010	O
10		108	Y	14010	O
11		109	B	650
12		110	B	650
13		116	P	9231
14		117	P	14059
15		118	P	14059
16		119	Q	1964	O
17		120	Q	1964	O
18		121	N	35825
19		129	N	8047
20		131	C	0	O
21		133	C	0	O
22		134	AE	30000	X
Sheet2

Cell Formulas
Range	Formula
F7	F7	=SUMIF(E2:E179,"X",D2:D179)+SUMPRODUCT(--(E2:E179="O"),--(MATCH(C2:C179&"",C2:C179&"",0)=ROW(C2:C179)-ROW(C2)+1),MAXIFS(D2:D179,C2:C179,C2:C179,E2:E179,"O"))

 

[FrankParker]

YES it works like a charm! You are a genius! Thanks!

 

[FrankParker]

Oh no, just tested it a little bit and found a problem: If you do not count with a circle the first one with the same categorie it doesn't add it to the sum at all somehow

 

[Eric W]

I'm not entirely sure what you want, but try this:

Book1
	A	B	C	D	E	F	G	H	I
1		id
2		94	V	0	X		Obtained	X
3		95	K	13135				OK	69089
4		96	W	8880
5		100	T	23115	X
6		102	O	24216
7		103	AQ	11435
8		105	Y	12956	O
9		107	Y	14010	O
10		108	Y	14010	O
11		109	B	650
12		110	B	650
13		116	P	9231
14		117	P	14059
15		118	P	14059
16		119	Q	1964	O
17		120	Q	1964	O
18		121	N	35825
19		129	N	8047
20		131	C	0	O
21		133	C	0	O
22		134	AE	30000	X
23
Sheet2

Cell Formulas
Range	Formula
I3	I3	=SUMIF(E2:E179,"X",D2:D179)+SUM(IF(COUNTIFS(C2:C179,C2:C179,E2:E179,"O"),MAXIFS(D2:D179,C2:C179,C2:C179)/COUNTIFS(C2:C179,C2:C179)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Note that this one is an array formula.

 

[FrankParker]

Oh, sorry, gonna try to be more specific this time: I am trying to make the circles sums only once between the same letter group, if there is more than one value within the group it should sum the higher one only, but if I choose one with a lower value it doesn't have to sum the highest value only should sum the value associated with the ID I have selected. Im gonna leave an image of what I was trying to achieve, maybe its better.

But you a really close to get what I was trying to do, really!

Thanks

 

[FrankParker]

Oh, sorry, gonna try to be more specific this time: I am trying to make the circles sums only once between the same letter group, if there is more than one value within the group it should sum the higher one only, but if I choose one with a lower value it doesn't have to sum the highest value only should sum the value associated with the ID I have selected. Im gonna leave an image of what I was trying to achieve, maybe its better.

View attachment 16342

But you a really close to get what I was trying to do, really!

Thanks

Oh no, just noticed that if I have three areas for the same letter group I can only sum the higher value or the lower value, but not the one in the middle.

 

[FrankParker]

sorry for the insistence but there is some way to achieve this?

 

[Eric W]

Sorry I haven't replied sooner, but I'm extremely confused as to what you want. In your example from post 7, for the Y group you want 14010 because one of the Y group is marked, and it's the highest in the group. For the P group you want 9231 because it's the only marked one, even though 1500 is higher. Right? But back to the Y group, the only one marked is 12956, so why don't you want that value?