Longest Column in multi-column Excel Range (in a single formula)?
excel, excel-formula
Solution
Maybe try this with CTRL+SHIFT+ENTER:
=MAX(SUBTOTAL(3,OFFSET(A:D,,COLUMN(A:D)-MIN(COLUMN(A:D)),,1)))
Any rectangular range can be used in place of `A:D`.
Problem
In a range where different columns may have different numbers of values (but there won't be gaps in the sequence within a column) I'd like to know the length of the longest column. To be more demanding, I'd like it in a single formula. So given a range like this: ``` | A B C D ---+---+---+---+--- 1 | 1 2 3 4 2 | 2 9 5 6 3 | 5 5 4 | 4 5 | ``` .. the answer I want is 4. Something that works in this case is ``` =MAX(COUNT(A1:A999),COUNT(B1:B999),COUNT(C1:C999),Count(D1:D999)) ``` But it's (a) ugly and (b) inflexible - doesn't cater for the case where a column might be added. I tried something like this, hoping for an array giving the set of column counts (hoping to wrap a `MAX()` around it): ``` {=COUNT(OFFSET(A1,0,COLUMN(A1:D1)-COLUMN(A1),999,1))} ``` .. but it only gives a single cell, which always seems to be the count on the first column. I know this could be easily solved in VBA but for several reasons (not least curiosity) I'd like to see if it can be solved in a single (probably array) formula.