Top Ten list based on an certain criteria.

Hello Excel Forum Saviours.

I am having a little bit of trouble creating a dynamic list in excel.

I am looking to create a top ten client list for an overview page. The forumla I have managed to put together so far is

=Large(IF('2014 Data'!L:L,E3,'2014 Data'!$S:$S,1)) - This currently returns the error of too many arguements.

Where L:L is the name of the Regional Manager which is confirmed by 'E3'. $S:$S is the revnue that is brought in.

I am trying to create a table that displays the top ten clients for each Regional Manager.

Any ideas?

Cheers

Tom

Hi Guys,

Thank you very much for your responses so far... I am currently working through a tutorial on Array formulas to get up to speed.

Yudlugar - I tried copying in your formula however it brought up a box similar to save as menu but titled Update Values: 2014Data1.

Any ideas what this is?

Unfortunately I would get in a lot of trouble if I uploaded the source table as it contains sensitive information, however from the reasearch I have done, once I have the top ten sorted by regional manager I will be able to use an Index and Match forumla to populate the client name field in the top ten table?

Kind Regards,

Tom

Sorry I put "2014Data1" when it should have been "2014 Data".

Replace sensitive data with dummy names/values. Just include enough lines that we can see how the data is set out.

I have created a very basic sample file, but should be able to transfer the forumla to my main spread sheet,

Once again thanks for all your help guys.

Tom

Please Login or Register  to view this content.

In E5 and copy down, it's an array formula so confirm with ctrl+shift+enter. Note , if you don't want duplicates you can get rid of the if count if bit

@Fotis, I like that, very neat. but why not replace ROW($C$2:$C$30)-ROW($C$2)+1 with ROW($C$1:$C$29)?

Also, I think we have a problem with both formulas. If there is a duplicate value for a person not named in E3 then it would still affect the formula. So I think:

=INDEX($A$2:$A$30,SMALL(IF($C$2:$C$30=F5,IF($B$2:$B$30=$E$3,ROW(E$1:E$29))),COUNTIF(F$5:F5,F5)))

Fostis and Yudlugar both absolute heros.

Fostis, one last little problem I am facing.

When copying the array in for the other two regional managers it is returning the top value for everyone with Row (A1) in every cell. As this is an array formula I can not change any of the individual cells... is there anything I am not doing to complete this?

Kind Regards,

Tom

See attachmentsample.xlsx

I changed Fotis' formula as suggested in previous post.

Thanks again guys, I have finally got it working for my spread sheet.

One quick question, dont know if it will work.

Some clients have 2 or 3 separate entries. Is there a way so that I could sum these so they only appear as one entry in the list.

Kind Regards,

Tom

That's a bit beyond me!
I think I've managed something that works...
for F5:
=LARGE(IF(B$2:B$26=E$3,SUMIF(A$2:A$26,"="&A$2:A$26,C$2:C$26)),ROW(A1)+SUM(IF(COUNTIF(A$2:A$26,"="&$E$4:E4)-1>0,COUNTIF(A$2:A$26,"="&$E$4:E4)-1)))
For E5:
=INDEX($A$2:$A$26,SMALL(IF(SUMIF(A$2:A$26,"="&A$2:A$26,C$2:C$26)=F5,ROW($C$2:$C$26)-ROW($C$2)+1),COUNTIF(F$5:F5,F5)))
Both array formulas.

Sorry me again....

The formula is also returning the 1st alphabetic client for the revenue value in the corresponding place in the list not taking into account the regional manager.

Any ideas?

Afraid not. I can't think how to get the regional manager clause to work within the sumif statement.

sample.xlsx@Fotis:

have a look at the attachment, I've got it to work providing the clients and managers are not mixed. If more than one manager has the same client it adds the total between them.

Could you maybe see if =SUMIFS will work where I use sumif to get the client, I only have 2003 and can't seem to work it out with sumproduct.

For what it's worth, I'm assuming it would be:
=LARGE(IF($B$2:$B$26=E$3,SUMIFS($A$2:$A$26,"="&$A$2:$A$26,$B$2:$B$26,"="&E$3,$C$2:$C$26)),ROW(A1)+SUM(IF(COUNTIF($A$2:$A$26,"="&$E$4:$E4)-1>0,COUNTIF($A$2:$A$26,"="&$E$4:$E4)-1)))
in F5 and
=INDEX($A$2:$A$26,SMALL(IF(SUMIFS($A$2:$A$26,"="&$A$2:$A$26,$B$2:$B$26,"="&E$3,$C$2:$C$26)=F5,ROW($C$2:$C$26)-ROW($C$2)+1),COUNTIF(F$5:F5,F5)))
in E5 but I never worked with sumifs and not sure on if it is valid.

Hi guys,

Really appreciate your continued support.

I have attached a full on example spread sheet so that there is no info missing.

The 2014 Data tab is the updateable data and the Top Clients Tab is where I would like to populate the list.

The Regional Managers are Coloumn I on 2014 data, with the Revnue being (M) and the Client names are in (A).

Some of the bigger clients are repeated as they have had to be split in to two teams due to their size. If possible I would like to combine these, though that may be impossible.

Thanks again,

Tom

Yeah 1076 was what I wanted, Thanks!

So Clark85, I think that the formulas you need are:
F5 (to find number): =LARGE(IF($B$2:$B$26=E$3,SUMIFS($C$2:$C$26,$A$2:$A$26,"="&$A$2:$A$26,$B$2:$B$26,"="E$3)),ROW(A1)+SUM(IF(COUNTIF($A$2:$A$26,"="&$E$4:$E4)-1>0,COUNTIF($A$2:$A$26,"="&$E$4:$E4)-1)))

E5 (to find Client name): =INDEX($A$2:$A$26,SMALL(IF(SUMIFS($C$2:$C$26,$A$2:$A$26,"="&$A$2:$A$26,$B$2:$B$26,"="E$3)=F5,ROW($C$2:$C$26)-ROW($C$2)+1),COUNTIF(F$5:F5,F5)))