Hi Guys,
Have one that I could not find documented anywhere. I did search as much as I can before posting but cant quite get the right formula for this task.
I am using this to perform half the task at hand
1. Look in column A for all cells that contain the word "EMEA"
2. For these cells only containing "EMEA" give me the MAX value of cell in column C
=MAX(IF(A:A="EMEA",C:C))
The above formula gives me the actual MAX value but what I actually what is for 1 and 2 to be logical statements (IF) and the final output should be the value in adjacent cell B
Column A = Region (APAC, EMEA, US, JPN) etc.
Column B = Agent Name
Column C = Number of contacts
So basically, performing the following:
1. Check column A for EMEA only
2. Check column C to find the largest number of contacts
3. Check column B return the name of the Agent.
My worksheet is actually using column's from A to AJ and each column has different data but the above are within the A:AJ range but for the purpose of asking my question I am just sticking with A:C
If I can get a formula that doesn't require (CTRL+SHIFT+ENTER) that would be amazing but I will use it if that's all we have.
Hope my question makes sense
Appreciate your support.
Have one that I could not find documented anywhere. I did search as much as I can before posting but cant quite get the right formula for this task.
I am using this to perform half the task at hand
1. Look in column A for all cells that contain the word "EMEA"
2. For these cells only containing "EMEA" give me the MAX value of cell in column C
=MAX(IF(A:A="EMEA",C:C))
The above formula gives me the actual MAX value but what I actually what is for 1 and 2 to be logical statements (IF) and the final output should be the value in adjacent cell B
Column A = Region (APAC, EMEA, US, JPN) etc.
Column B = Agent Name
Column C = Number of contacts
So basically, performing the following:
1. Check column A for EMEA only
2. Check column C to find the largest number of contacts
3. Check column B return the name of the Agent.
My worksheet is actually using column's from A to AJ and each column has different data but the above are within the A:AJ range but for the purpose of asking my question I am just sticking with A:C
If I can get a formula that doesn't require (CTRL+SHIFT+ENTER) that would be amazing but I will use it if that's all we have.
Hope my question makes sense
Appreciate your support.