Help With A Formula To Sort Numbers And Percentages

Ha! One that didn't cause TOO much brain strain. Use variants of this array formula, as shown in the sheet:

=IFERROR(INDEX(A:A,SMALL(IF(($B$3:$K$3=$AT$2)*ROW($B$4:$K$10)*($B$4:$K$10<=$AU$2),ROW($A$4:$A$10)),ROWS($AT$3:AT3)))&"","")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Maybe you just need to add another clause:

=IFERROR(INDEX(A:A,SMALL(IF(($B$3:$K$3=$AT$2)*ROW($B$4:$K$10)*($B$4:$K$10<=$AU$2)*($B$4:$K$10>=$AV$2),ROW($A$4:$A$10)),ROWS($AT$3:AT3)))&"","")

How will that work? Will ONLY one of the two cells (AU2 and AV2) be populated, or can BOTH be populated with a number?

There are now too many categories to to set up independent formulae. So the relatively simple formula is now a bit of a monster. It's done. The parameter choice and the >= and <= choices are now selected from DD boxes, to prevent extraneous spaces creeping in.

I'm away till the UK morning. Look back here at breakfast coffee your time tomorrow.

Named ranges are a bit like shorthand. They enable words to be used to describe a range of cells - some people prefer this to $A14:$A$14532 or whatever. Hit CTRL F3 to see the two Named Ranges in your file. I have given them the highly original names Range1 and Range2. NRs have the advantage that they can be made dynamic, allowing you to add/substract values and the rangfe automatically adjusts to suit. I didn't bother with that here, as the categories used seemed as though they didn't ned to be dynamic: <= and >= cover all the possibilities... so a simple formula to describe the Named range is fine =DV!$A$1:$A$2 However if you needed to add/subtract values from range2, then the DV formula should be edited to be:

='DV'!$B$1:INDEX('DV'!$B$1:$A$100,COUNTA('DV'!$B$1:$B$100))

I've replaced Range2 with List which is dynamic. Add a few lines to column B. see what happens in OI2. Delete them. see what happens in OI2...