MAx formula - Tedious process of writing all arguments individually

H

Hari

Hi

I want to find maximum of rows from many contiguous columns.

My formula in cell IV65536 is
=MAX(COUNTA(B2:B1000),COUNTA(C2:C1000),COUNTA(D2:D1000),COUNTA(E2:E1000),COU
NTA(F2:F1000),COUNTA(G2:G1000),COUNTA(H2:H1000),COUNTA(I2:I1000),COUNTA(J2:J
1000),COUNTA(K2:K1000),COUNTA(L2:L1000),COUNTA(M2:M1000),COUNTA(N2:N1000),CO
UNTA(O2:O1000),COUNTA(P2:p1000),COUNTA(Q2:Q1000),COUNTA(R2:R1000),COUNTA(S2:
S1000),COUNTA(T2:T1000),COUNTA(U2:U1000),COUNTA(V2:V1000),COUNTA(W2:W1000),C
OUNTA(X2:X1000),COUNTA(Y2:Y1000),COUNTA(Z2:Z1000),COUNTA(AA2:AA1000),COUNTA(
AB2:AB1000),COUNTA(AC2:AC1000),COUNTA(AD2:AD1000),IU65536)+1

My formula in IU65536 which is part of the formula above is =
MAX(COUNTA(AE2:AE1000),COUNTA(AF2:AF1000),COUNTA(AG2:AG1000),COUNTA(AH2:AH10
00),COUNTA(AI2:AI1000),COUNTA(AJ2:AJ1000),COUNTA(AK2:AK1000),COUNTA(AL2:AL10
00))

I had to reference to IU655536 because of Excel's limitation of having only
30 arguments. Anyway my question is different here. I want to know whether
there is a shorter formula for accomplishing what I want to do ( Im asking
only from curiosity, otherwise the present method works well). What I mean
is rather than writing each column individually ( B2:B1000, C2:C1000 and so
on...) is there a way to write the formula in such a way that one doesnt
have to type all these contiguous columns manually one after one.

Regards,
Hari
India
 
M

Myrna Larson

The simplest is an extra "helper" row, say row 1002: in B1002 put the formula
=COUNTA(B2:B1000). Copy it across to AL1002. You can hide row 1002, or move it
farther down the worksheet if necessary. Then your MAX formula reduces to
=MAX(B1002:AL1002).

If that's not an option, there's VBA:

Function MaxColumnCount(Rng As Range) As Variant
Dim c As Long
Dim Max As Double
Dim N As Double

Max = 0
For c = 1 To Rng.Columns.Count
N = Application.CountA(Rng.Columns(c))
If N > Max Then Max = N
Next C
MaxColumnCount = Max
End Function

After you've put the code in a standard module in your workbook, use this
formula =MaxColumnCount(B2:AL1000) in your worksheet cell.
 
H

Hari

Hi Myrna,

Thanx a ton.

a) The logic and elegancy of writing a formula in cell 1002 is very very
good.

b) I learned something from ur VBA code. I didnt know that one could call a
function from within a workbook. Is this what is referred to as UDF?

Regards,
Hari
India
 
Top