How to show value a sales name by reference a high sales number

[eddorena]

Hi there,

I need help with the proper formula to show in F13 the name agent Tanjung with reference he have the highest commission 300,000, and F14 is amount 300,000

I already try with sum function, however, not work properly.

Thank you in advance for the help and advice

 

[Fluff]

How about

=LET(u,UNIQUE(E2:E11),INDEX(SORT(CHOOSE({1,2},u,SUMIFS(F2:F11,E2:E11,u)),2,-1),1,{1;2}))

 

[eddorena]

How about

=LET(u,UNIQUE(E2:E11),INDEX(SORT(CHOOSE({1,2},u,SUMIFS(F2:F11,E2:E11,u)),2,-1),1,{1;2}))

Hi Fluff,

Thank you, it's working and I tested to change the agent name with large commission and the name also change

And thank you so much to make me learn a new formula "LET" that I never used before (I still wondering why use "u" and still looking the proper link to understand "LET" formula)

 

[eddorena]

Hi @Fluff

I just realized if the amount auto appears under agent name and it may because this formula "SUMIFS(F2:F11,E2:E11,u)" and I tested to remove that formula and the result only the agent name (as my expectation at the beginning), however, not the agent name that have a big commission (agent name change to Andika from Tanjung)

(in parallel I try to modify) whether possible if the result only agent name with expectation for the commission field will use "sumproduct" with reference agent name above

Thank You ?

 

[Fluff]

I don't understand what you are saying. Can you post some sample data that shows the problem, along with an explanation of what is wrong.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[eddorena]

I don't understand what you are saying. Can you post some sample data that shows the problem, along with an explanation of what is wrong.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Fluff,

Sorry for making you confused, just ignored since I already completely understand about all formula that you advise (LET, UNIQUE,INDEX,SORT,CHOOSE, and common formula SUMIFS)

Now I just wonder how making the result become vertical (Agent name, Comission) I tried to add TRUE in by_col for formula SORT but the result still horizontal

Before : =LET(n,UNIQUE(E2:E11),INDEX(SORT(CHOOSE({1,2},n,SUMIFS(F2:F11,E2:E11,n),n),2,-1),1,{1;2}))

After : =LET(n,UNIQUE(E2:E11),INDEX(SORT(CHOOSE({1,2},n,SUMIFS(F2:F11,E2:E11,n),n),2,-1,TRUE),1,{1;2}))

Note : I change "u" with "n"

Thank You

 

[Fluff]

The formula does return the values vertically. Did you change the formula to allow for your local settings?
If so what did you change it to?

 

[eddorena]

The formula does return the values vertically. Did you change the formula to allow for your local settings?
If so what did you change it to?

I see, duly noted.

When I tried to understand about formula "LET" I just realize if the "u" from "=LET(u.." is variable name that can changed, so to make me easy remember I change to "n" (name).

I really appreciate your help, especially for the new solution with many formula (I believe my colleague will confused and to hard to understand), greetings from Indonesia @Fluff ?

 

[Fluff]

Did you need to change the commas in the formula to semi-colons for the formula to work?

 

[eddorena]

Did you need to change the commas in the formula to semi-colons for the formula to work?

No, the original formula below 100% working at begin and I didn't change the commas.

Excel Formula : =LET(u,UNIQUE(E2:E11),INDEX(SORT(CHOOSE({1,2},u,SUMIFS(F2:F11,E2:E11,u)),2,-1),1,{1;2}))