2

enter image description here

Hi everyone,

What I want to achieve in this task is:

  • If AAPL appeared the most in the winning category, then the Best performance will be AAPL regardless of whether the total P/L (total P/L for winning & lossing) for AAPL is higher or lower than ROKU. For example in the screenshot above, AAPL appeared 4 times in winning category with total P/L of 151.5 while ROKU appeared 2 times with total P/L of 187. So the Best performance will be AAPL instead of ROKU even though the P/L for ROKU is higher.

  • However, if the frequency for both AAPL and ROKU appeared in the winning category are the same, then the Best performance will be determined by total P/L. So in this case, if the total P/L for ROKU is still higher than AAPL, then the Best performance will be ROKU.

I tried to use SUMIFS but it only allow me to have a sum range which result in 291.5. The formulas in cell H6 is =IF(B6:B215<>"",COUNTIF(B6:B215,B6:B215),""), cell I6 is =MAX(H6:H215) and cell J6 is =MAX(SUMIFS(C6:C215,B6:B215,B6:B215,H6:H215,$I$6)) as shown in the screenshot above. I think what I did is not a right one but I'm not sure what can be modified. The correct output should be the answers in cell C2:C3.

Please give me some advice on how should I modify my formulas in order to achieve this. Any help will be greatly appreciated!

Edit

enter image description here

  • List item
weizer
  • 1,009
  • 3
  • 16
  • 39

2 Answers2

2

You can achieve it with any helping column if you have Excel O365. Try below formula to C2 cell

=@LET(x,INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,UNIQUE(B6:B11),COUNTIF(B6:B11,UNIQUE(B6:B11)),SUMIF(B6:B11,UNIQUE(B6:B11),C6:C11)+SUMIF(E6:E11,UNIQUE(B6:B11),F6:F11))&"</s></t>","//s"),TRANSPOSE(SEQUENCE(COUNTA(UNIQUE(B6:B11))+1,2))),SORTBY(x,INDEX(x,,2),-1,INDEX(x,,3),-1))

Then put below formula to C3 cell

=SUMIF(B6:B11,C2,C6:C11)+SUMIF(E6:E11,C2,F6:F11)

enter image description here

Edit: Try below formulas as per my screenshot with helping columns for Non 365 version of excel.

H6=IF($B$6:$B$11<>"",COUNTIF($B$6:$B$11,$B$6:$B$11),"")
I6=SUMIF($B$6:$B$11,B6,$C$6:$C$11)+SUMIF($E$6:$E$11,B6,$F$6:$F$11)
J6=H6*10^MAX($H$6:$H$11)+I6
C2=INDEX(IF(J6:J11=MAX(J6:J11),B6:B11,""),1) -->Array entry (CRTL+SHIFT+ENTER)
C3=SUMIF(B6:B11,C2,C6:C11)+SUMIF(E6:E11,C2,F6:F11)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Thanks for your input. However, I'm doing this for 100+ users. I'm not able to guarantee every user has Excel 365. Do you have other method without using function in excel 365? – weizer May 03 '21 at 06:30
  • Then we have to go with helper column like you used `H` column. – Harun24hr May 03 '21 at 06:32
  • ya, I'm still thinking on other method to work on this issue with the help of helper column – weizer May 03 '21 at 06:36
  • I think it should work well, the only thing is if I change cell `B11` to `ROKU`, then cell `C2` will not show anything. I guess this is because of the `Row_num` in `INDEX` – weizer May 03 '21 at 07:45
  • Make sure you entered `INDEX(...)` formula as array entry. `CTRL+SHIFT+ENTER`. – Harun24hr May 03 '21 at 07:55
  • yes, i'm using array entry. I guess that is because of the row num in `INDEX`. I attached my screenshot under the edit section in my question. In this case, the first row is actually empty. – weizer May 03 '21 at 08:01
  • 1
    Can you please try this `=INDEX(B6:B11,SMALL(IF(J6:J11=MAX(J6:J11),ROW(B6:B11)-ROW(B5),""),1))` – Harun24hr May 03 '21 at 08:05
  • Yes, it work perfectly now:). Thank you very much! May I know what is the reason that cause you to use the formula in cell `J6`? – weizer May 03 '21 at 08:38
  • Without it you can't detect highest value winner. – Harun24hr May 03 '21 at 08:39
  • I found out that the way you did will not always working if there is an extremely high P/L in Winning category. I attached the screenshot under `Edit` where it show an example where the output is wrong. The correct answer should be `AAPL` instead of `TSM`. – weizer May 03 '21 at 15:01
  • 1
    Then make it 100 instead of 10 like `H6*100^MAX....` – Harun24hr May 03 '21 at 15:33
1

A slightly different approach for the Excel 365 version:

=INDEX(LET(UNIQUES,UNIQUE(B6:B11),SORTBY(UNIQUES,COUNTIF(B6:B11,UNIQUES),-1,SUMIF(B6:B11,UNIQUES,C6:C11)+SUMIF(E6:E11,UNIQUES,F6:F11),-1)),1)

enter image description here

If one of the AAPL in the first column is changed to ROKU

enter image description here

The pre-365 equivalent of the above formula should be:

=INDEX(
IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),MATCH(B6:B11,B6:B11,0))>0,B6:B11),
MATCH(
MAX(
COUNTIF(B6:B11,IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),MATCH(B6:B11,B6:B11,0))>0,B6:B11))*10^4+SUMIF(B6:B11,IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),MATCH(B6:B11,B6:B11,0))>0,B6:B11),C6:C11)+SUMIF(E6:E11,IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),  MATCH(B6:B11,B6:B11,0))>0,B6:B11),F6:F11)
),
COUNTIF(B6:B11,IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),MATCH(B6:B11,B6:B11,0))>0,B6:B11))*10^4+SUMIF(B6:B11,IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),MATCH(B6:B11,B6:B11,0))>0,B6:B11),C6:C11)+SUMIF(E6:E11,IF(FREQUENCY(MATCH(B6:B11,B6:B11,0),MATCH(B6:B11,B6:B11,0))>0,B6:B11),F6:F11),0)
)

where I have used Frequency to simulate Unique and have multiplied the Countif by 10^4 before adding it to the Sumif's to simulate Sortby with two sort keys.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thanks for your input, do you have any method that can be used for non Excel 365 version? – weizer May 03 '21 at 15:01
  • I think so - use Frequency instead of Unique and a workaround to replace Sortby. Will add to my answer, but I can't actually test it in Excel pre-365. – Tom Sharpe May 03 '21 at 15:20
  • I think if the function is available in Excel pre-365 then should be fine, I can test it out. Initially I tried to use `=INDEX(B6:B11,MODE.MULT(IFNA(MATCH(B6:B11,B6:B11,0),"")))` to get the mode in column `B6:B11`, but I realized `MODE.MULT` is not working in Excel pre-365. – weizer May 03 '21 at 15:31