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(D21000),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(P21000),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
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(D21000),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(P21000),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