Excel 2000: Get max value based on a condition
Posted by Melody on April 10, 2001 7:39 AM
I need to find out how to get the maximum value of a cell based on the condition of another cell.
For instance, if column "A" has salesman codes, and column "B" has the sales amount of each sale, how do I find the large sale for Salesman #1, etc.?
Col A Col B
What I have been doing in the past is creating a new column for each salesman, and using =IF(A2=#####,B2,""). Once I have pasted that formula down, then I've simply gone for =large(W2:5000,1). However, there has GOT to be an easier way that I don't have to do the new column creation thing. I'm running out of columns!
Thanks for your help!!!