The longest column in Excel multi-column range (in one formula)?

Question

In the range where different columns can have different values ​​(but there will be no spaces in the sequence inside the column), I would like to know the length of the longest column. To be more demanding, I would like to get it in one formula.

So, set this range:

| ABCD ---+---+---+---+--- 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,

 =MAX(COUNT(A1:A999),COUNT(B1:B999),COUNT(C1:C999),Count(D1:D999)) 

But it is (a) ugly and (b) inflexible - not suitable for the case when you can add a column.

I tried something like this, hoping the array would provide a set of columns (in the hope of wrapping MAX() around it):

 {=COUNT(OFFSET(A1,0,COLUMN(A1:D1)-COLUMN(A1),999,1))} 

.. but it gives only one cell, which is always considered the counter in the first column.

I know this can be easily solved in VBA, but for several reasons (not least curiosity). I would like to see if it can be solved in one formula (probably an array).

Answer 1

Perhaps try this with CTRL + SHIFT + ENTER:

 =MAX(SUBTOTAL(3,OFFSET(A:D,,COLUMN(A:D)-MIN(COLUMN(A:D)),,1))) 

Instead of A:D you can use any rectangular range.