Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array of
scalars is created here. That is the nature of array formulae.

That is correct.

COUNTIF(ALIST,"<"&ALIST)+COUNT(AList)

If AList refers to A1:A5 then this is how the array breaks down:

COUNTIF(A1:A5,"<"&A1)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A2)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A3)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A4)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A5)+COUNT(A1:A5)

--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Ken Johnson wrote:
I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range
named AList.
The original formula was supplied by Harlan Grove in Jun 2004...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()-
ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0))

This array formula works perfectly; however, I am having trouble
understanding how it works.

The SMALL function’s first argument, COUNTIF(AList,"<"&AList)
+COUNT(AList), has to return an array.
This is the bit I can’t understand.
Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList)
manages to return an array for the SMALL function’s first argument?

Ken Johnson

Hi Ken,

Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array of
scalars is created here. That is the nature of array formulae.

You can test this by placing the cursor on a cell with the formula and
using the formula evaluator under Tools | Formula Auditing | Evaluate
Formula. Try it on a fairly small sample (3-4 rows) of data.

FWIW SMALL is just as happy to accept a single constant for its first
argument, even though it seems pointless to do so:

=SMALL(5,1) returns 5

Cool find, by the way!

Hope this helps.