Have searched all over for a solution to this problem but cannot seem to find a complete fix.
Say for example I have two columns
Column A contains Names of sales consultants.
Column B contains Names of products sold.
I want to return the value in column B if it is the most frequent based on a condition from column A.
So:
John Smith Banana
John Smith Banana
John Smith Apple
Eric Jones Apple
Eric Jones Apple
Eric Jones Apple
Eric Jones Apple
Eric Jones Mango
John Smith Apple
John Smith Banana
John Smith Orange
So if the condition was John Smith the answer would be Banana.
Now I can figure out the most common fruit using this array INDEX(B2:B500,MATCH(MAX(COUNTIF(B2:B500,B2:B500)),COUNTIF(B2:B500,B2:B500),0))
This would return Apple as it's the most frequent fruit in column B; however I need the formula to only look at rows which contain John Smith and then return the most popular fruit he has sold. Have tried nesting an if statement in the array and it does not work have a feeling a need to nest another index and match in there but it keeps falling down.
Anybody who solves gets a cookie.
ps I can do this using a combination of vba and formulas but that is not an ideal solution to wht I need. Also I really need this doing in a single array.
Say for example I have two columns
Column A contains Names of sales consultants.
Column B contains Names of products sold.
I want to return the value in column B if it is the most frequent based on a condition from column A.
So:
John Smith Banana
John Smith Banana
John Smith Apple
Eric Jones Apple
Eric Jones Apple
Eric Jones Apple
Eric Jones Apple
Eric Jones Mango
John Smith Apple
John Smith Banana
John Smith Orange
So if the condition was John Smith the answer would be Banana.
Now I can figure out the most common fruit using this array INDEX(B2:B500,MATCH(MAX(COUNTIF(B2:B500,B2:B500)),COUNTIF(B2:B500,B2:B500),0))
This would return Apple as it's the most frequent fruit in column B; however I need the formula to only look at rows which contain John Smith and then return the most popular fruit he has sold. Have tried nesting an if statement in the array and it does not work have a feeling a need to nest another index and match in there but it keeps falling down.
Anybody who solves gets a cookie.
ps I can do this using a combination of vba and formulas but that is not an ideal solution to wht I need. Also I really need this doing in a single array.