Arranging values from Series of Arrays in Descending Order by Formula - Not Macro

In my formula you could replace COUNT/COUNTA with SUMPRODUCT which removes the problem of numeric vs text data

e.g.

=IFERROR(INDEX($C$5:$K$10,MOD((ROWS($1:1)-1),SUMPRODUCT((LEN($B$5:$B$10)>0)+0))+1,((INT((ROWS($1:1)-1)/COUNT($B$5:$B$10))*2+1))),"")

Unfortunately, you cannot use this in a named range (Jason's "Row_Count")

Originally Posted by JohnTopley

Unfortunately, you cannot use this in a named range (Jason's "Row_Count")

Any reason why not, John?

It works fine as far as I can see, the only difference being that the range needs to be qualified with the sheet name when used as a named range.

Although I would use =MATCH(2,1/LEN($B$5:$B$10)) as a preference (if there was a blank row in the middle of the data range, sumproduct would drop the last row). That said, my formula is not meant to cope with empty rows in the middle of the data range, so this method would highlight the fact that there is a problem by throwing out a sheet full of errors, but not be able to resolve them.