Count the Maximum contribution factor for every vendor

Hi All,

Hope you are doing well!.. I am trying to get the maximum count of the contribution factors for every vendor.. And I am trying to search for the words -Promotion,Availability,BuyBox in the contribution column and identify the one with the maximum count for every vendor ..The contribution field has multiple text along with the words Promotion,Availability,BuyBox which is the challenge that i am facing ...Please find attached the excel file with the expected output..Can you pls help with the same...

Thanks,
Arun

Try these array formulas, in F2

Formula:

=IFERROR(INDEX($A$2:$A$21,MATCH(TRUE,ISERROR(MATCH($A$2:$A$21,$F$1:$F1,0)),0)),"")

and G2

Formula:

=IF(F2="","",INDEX($K$1:$K$4,MATCH(MAX(COUNTIFS($A$2:$A$21,F2,$C$2:$C$21,"*"&$K$1:$K$4&"*")),COUNTIFS($A$2:$A$21,F2,$C$2:$C$21,"*"&$K$1:$K$4&"*"),0)))

With a list of the words to search for in $K$1:$K$4. Note that if you copy and paste the words from your sample, there is a stray space at the end of 'Availability ' which needs to be removed of the second formula will fail.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hi Jason,

This is awesome!!!!...Really appreciate your help!!!...

Thanks,
Arun