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
Slsmn Sales
10185 48.55
16582 95.14
10185 10.15
10185 58.77
16582 11.10
27572 50.00

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!!!

Posted by Mark W. on April 10, 2001 8:24 AM

Have you considered...

using a PivotTable?

Posted by Stephane Parent on April 10, 2001 8:30 AM

Hi Melody,
Try typing this and pressing ENTER while holding shift+ CTRL
=MAX(IF(a1:a10=salesman number,b1:b10))

the formula will look like this:
{=MAX(IF(a1:a10=saleman number,b1:b10))}

a1:a10 is the column with the salesmen number and b1:b10 is the column with sales
salesman number is the specific salesman you're looking for. It could also be a ref. Using this method, it's easy to maintain a permanent summary table of all salesmen results (max, min, average, number of sales, etc)
This is call array formula. When ever you change it, you must press the shift+CTRL+ENTER keys

I hope it helps

Stephane Parent

Posted by Melody on April 10, 2001 11:18 AM

THANKS!!!!!!!!

That worked -- THANKS!!!!!!!!!!!!!!!