Formula using ROWS, COUNTIF, INDEX, and SMALL

Hi guys, Some of you helped me out in the past by creating this formula, and now I'm trying to alter it to fit another circumstance.

=IF(ROWS(AW$3:AW3)>COUNTIF($AF$3:$AF$848,"100%"),"",INDEX($B$3:$B$848,SMALL(INDEX(($AF$3:$AF$848="100%")*(ROW($AF$3:$AF$848)-ROW($AF$3)+1),),COUNTIF($AF$3:$AF$848,"100%")+ROWS(AW3:AW$3))))

It's supposed to create a list of the names of people with %100, but instead it's listing the first person for every person that has 100%.

For example, if two random people have 100%, the first two people on the list are generated. If three random people have %100, the first three people will be listed. I would like my generated list to show only those who have %100.

A dummy sheet is attached.

dummy 10.xls

Thanks in advance!

Sorry, I'm afraid that doesn't do anything.

Hello
Try the following in cell AX3 copied down to AX13:

Please Login or Register  to view this content.

It's a little long, as it repeats with the IF/ISERROR functions to hide errors. If you're using 2007 or later, then it could be shortened with the IFERROR function.

Hope this helps.
DBY

Hi, DBY, thanks for your help. So, here's the thing. What you created works great in my dummy sheet. It does exactly what I want it to. The problem is, when I transfer the formula to my actual document, it doesn't work. The list of names, for starters, is 848 rows long. Which is fine, I mean, I've adjusted the formula from 13 to 848, but still it doesn't work.

Could it be because the 100% in column AF is the result of a formula, and not just a number?

Hello
Didn't realize how big the list of names would be . Depending on how many potential names at 100% there might be, you're going to need at least that amount of formulas, so things might slow down during calculation if you've got hundreds of these formulas. If you have no values in the 'Percentage' Column in certain rows, then the formula needs to be amended to account for this. Alternatively, a Pivot table might be a better option but your data needs to be set out correctly with column headers.

However, take a look at the attached reply on the 'Example' sheet (I've put in around a 1000 John Does) for some ideas.

DBY

Wow, that's pretty intense. I've got 65 people at 100% so far, and it's likely to stay under 100. That said, this stuff appears to be a bit over my head. I really appreciate all your hard work, I'm just afraid I can translate it into my working knowledge.

Sorry it's a little difficult to grasp, those formulas do seem a little intimidating when first looking at them but as I said it does repeat itself in order to hide the errors where no value is found. I can't think of a simpler method at the moment unless you resort to using VBA.

Regards
DBY

Okay, here's a question for you, if I create a second column, which does a simple IF statement and displays the person's name if they have 100%, can I take that column and consolidate it into a list somewhere?

Does that make sense? Basically in a column beside every person that has 100%, there name will appear. Then create a list without blank spaces of all the names in that column.

Where would you be placing your consolidated List? If on another sheet a Pivot table could do that. Otherwise you still need formulas to bring back the names. The advanced filter will provide you with a consolidated list but needs to be run each time the records are updated.

I see. So is it possible there's a simpler formula for this though? The list would be on the same sheet in a hidden column.

I assume you mean the helper column with the If formulas would be hidden not the actual List of names? The formulas can be shortened with an helper column and conditional formatting could hide the errors but you still need 1 formula for each name, no way round that I'm afraid. If you have a hundred names with 100% rating, you're going to need 100 formulas to return them all and so on. It might be possible to have a scrollable list however, with a limited number of formulas.

So, I know this isn't going to help any, but I'm already using the first formula I showed you in a different part of the spread sheet, but instead of 100% it's looking for R and it works fine, up to 69 names so far. Is there a difference between the letter R (which is simply text), and a formula showing 100%?

The 'R' is a text value and as such needs the "" quotes around it. It is just the criteria you are using to select which names to return. To reference the 'Percentage' column to return names with 100%, you would have to replace the 'R' range referenced with the 'Percentage' range and set the criteria to 1 ( no quotes around it) assuming your 'Percentage' column is formatted as percentage. 1 is the underlying value of 100% as Excel sees it, stripped of its percentage formatting.

DBY

So if I use this formula: (sorry, I changed column AW -> AX on my sheet)

=IF(ROWS(AX$3:AX3)>COUNTIF($AF$3:$AF$848,1),"",INDEX($B$3:$B$848,SMALL(INDEX(($AF$3:$AF$848=1)*(ROW($AF$3:$AF$848)-ROW($AF$3)+1),),COUNTIF($AF$3:$AF$848,"<>1")+ROWS(AX3:AX$3))))

It sort of works.

It takes some of the names with 100% and puts them in a list without spaces. In this case, I have 98 people now with 100%, yet the list it compiles only shows 34 of these people. I can't understand why it's not showing everyone.

Not sure about the workbook you're using but this formula in the example file you uploaded copied down from cell AX3 works for me:

Please Login or Register  to view this content.

DBY

If only it worked in my main spreadsheet. Sigh. I'm not sure what the problem is. I might just have to accept reality.

I really want to thank you for all your help. I'm amazed at the dedication from some posters on this website. Thanks again.