FrankParker
New Member
- Joined
- Jun 16, 2020
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
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!
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!