Large function based on a condition

[Daniel Bonallack]

I want to get the 5 companies with the largest sales,
based on a certain condition.

So in Col A I have company names. In column B, I have
their city location (New York or London). And in column
C, their sales.

In cells E1:E5 I'd like to return the companies based in
New York with the 5 highest sales.

Can anyone help with this?

Thanks in advance
Daniel

 

[Jim]

Have a look at Data>Filter>Autofilter or Data>Pivot Table. I recommend
Autofilter unless you prefer a worksheet formula.

 

[Chris Leonard]

Why not use the data/sort menu.

Sort first by column B, then By Column C (descending)

Look for New York and take the first five!

You could also use the RATE function in a further column and get an order
this way, not sure how to put the output into the first 5 columns in E
however without some VB .... anyone else know.

Here is my RATE function: =IF(B1="New York",RANK(C1,C:C),-1)

Hope this helps

Chris

 

[Harlan Grove]

I want to get the 5 companies with the largest sales,
based on a certain condition.

So in Col A I have company names. In column B, I have
their city location (New York or London). And in column
C, their sales.

In cells E1:E5 I'd like to return the companies based in
New York with the 5 highest sales.

If the full data range were A3:C102, you could try the following array formula.

G3:H7
=INDEX($A$3:$C$102,LARGE(IF($B$3:$B$102="New York",$C$3:$C$102),{1;2;3;4;5}),
{1,3})