Formula Exceeds Character Limit

[andyball2311]

I have a formula that is too long for an excel 2010 cell. I will post it below, but the basic premise is that I have a spreadsheet that has 26 columns and > 8000 rows of data, many of which are n/a errors by design. For each row I want my formula to return the sum of the first 6 columns that have a value in them. So if a row read like this:

6 7 3 n/a 4 n/a 5 7 8

I would wan the number 32 returned. To do this I was using if(isna), but exceeded my character limit. I am very limited in VBA and can't think of a good way to break this up so any help is appreciated.

=IF(ISNA(C2),IF(ISNA(D2),IF(ISNA(E2),IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),(Z2+IF(ISNA(AA2),0,AA2))),(Y2+IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),Z2)+IF(ISNA(AA2),0,AA2))),(X2+ Y2+Z2+AA2)),(W2+X2+Y2+Z2+AA2)),(V2+W2+X2+Y2+Z2+AA2)),(U2+V2+W2+X2+Y2+Z2)),(T2+IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),Z2),Y2),X2),W2),V2),U2)+ IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),Z2),Y2),X2),W2),V2)+ IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),Z2),Y2),X2),W2)+ IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),Z2),Y2),X2)+ IF(ISNA(Y2),IF(ISNA(Z2),IF(ISNA(AA2),0,AA2),Z2),Y2))),(S2+IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),0,Z2),Y2),X2),W2),V2),U2),T2)+ IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),0,Z2),Y2),X2),W2),V2),U2)+ IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),0,Z2),Y2),X2),W2),V2)+ IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),0,Z2),Y2),X2),W2)+ IF(ISNA(X2),IF(ISNA(Y2),IF(ISNA(Z2),0,Z2),Y2),X2))),(R2+IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),0,Y2),X2),W2),V2),U2),T2),S2)+ IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),0,Y2),X2),W2),V2),U2),T2)+ IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),0,Y2),X2),W2),V2),U2)+ IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),0,Y2),X2),W2),V2)+ IF(ISNA(W2),IF(ISNA(X2),IF(ISNA(Y2),0,Y2),X2),W2))),(Q2+IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),0,X2),W2),V2),U2),T2),S2),R2)+ IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),0,X2),W2),V2),U2),T2),S2)+ IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),0,X2),W2),V2),U2),T2)+ IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),0,X2),W2),V2),U2)+ IF(ISNA(V2),IF(ISNA(W2),IF(ISNA(X2),0,X2),W2),V2))),(P2+ IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),0,W2),V2),U2),T2),S2),R2),Q2)+ IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),0,W2),V2),U2),T2),S2),R2)+ IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),0,W2),V2),U2),T2),S2)+ IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),0,W2),V2),U2),T2)+ IF(ISNA(U2),IF(ISNA(V2),IF(ISNA(W2),0,W2),V2),U2))),(O2+ IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),0,V2),U2),T2),S2),R2),Q2),P2)+ IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),0,V2),U2),T2),S2),R2),Q2)+ IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),0,V2),U2),T2),S2),R2)+ IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),0,V2),U2),T2),S2)+ IF(ISNA(T2),IF(ISNA(U2),IF(ISNA(V2),0,V2),U2),T2))),(N2+IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2),P2),O2)+ IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2),P2)+ IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2)+ IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2)+ IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2))),(M2+IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2),P2),O2),N2)+ IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2),P2),O2)+ IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2),P2)+ IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2),Q2)+ IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),IF(ISNA(U2),0,U2),T2),S2),R2))),(L2+IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),0,T2),S2),R2),Q2),P2),O2),N2),M2)+ IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),0,T2),S2),R2),Q2),P2),O2),N2)+ IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),0,T2),S2),R2),Q2),P2),O2)+ IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),0,T2),S2),R2),Q2),P2)+ IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),IF(ISNA(T2),0,T2),S2),R2),Q2))),(K2+IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),0,S2),R2),Q2),P2),O2),N2),M2),L2)+ IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),0,S2),R2),Q2),P2),O2),N2),M2)+ IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),0,S2),R2),Q2),P2),O2),N2)+ IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),0,S2),R2),Q2),P2),O2)+ IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),IF(ISNA(S2),0,S2),R2),Q2),P2))),(J2+IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),0,R2),Q2),P2),O2),N2),M2),L2),K2)+ IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),0,R2),Q2),P2),O2),N2),M2),L2)+ IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),0,R2),Q2),P2),O2),N2),M2)+ IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),0, R2),Q2),P2),O2),N2)+ IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),IF(ISNA(R2),0,R2),Q2),P2),O2))),(I2+ IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),0,Q2),P2),O2),N2),M2),L2),K2),J2)+ IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),0,Q2),P2),O2),N2),M2),L2),K2)+ IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),0,Q2),P2),O2),N2),M2),L2)+ IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),0,Q2),P2),O2),N2),M2)+ IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),IF(ISNA(Q2),0,Q2),P2),O2),N2))),(H2+IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2),J2),I2)+ IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2),J2)+ IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2)+ IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2)+ IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2))),(G2+IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2),J2),I2),H2)+ IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2),J2),I2)+ IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2),J2)+ IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2),K2)+ IF(ISNA(L2),IF(ISNA(M2),IF(ISNA(N2),IF(ISNA(O2),IF(ISNA(P2),0,P2),O2),N2),M2),L2))),(F2+ IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),0,M2),L2),K2),J2),I2),H2),G2)+ IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),0,M2),L2),K2),J2),I2),H2)+ IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),0,M2),L2),K2),J2),I2)+ IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),0,M2),L2),K2),J2)+ IF(ISNA(K2),IF(ISNA(L2),IF(ISNA(M2),0,M2),L2),K2))), (E2+ IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),0,L2),K2),J2),I2),H2),G2),F2)+ IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),0,L2),K2),J2),I2),H2),G2)+ IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),0,L2),K2),J2),I2),H2)+ IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),0,L2),K2),J2),I2)+ IF(ISNA(J2),IF(ISNA(K2),IF(ISNA(L2),0,L2),K2),J2))),(D2+ IF(ISNA(E2),IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),0,K2),J2),I2),H2),G2),F2),E2)+ IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),0,K2),J2),I2),H2),G2),F2)+ IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),0,K2),J2),I2),H2),G2)+ IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),0,K2),J2),I2),H2)+ IF(ISNA(I2),IF(ISNA(J2),IF(ISNA(K2),0,K2),J2),I2))),(C2+IF(ISNA(D2),IF(ISNA(E2),IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),0,J2),I2),H2),G2),F2),E2),D2)+ IF(ISNA(E2),IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),0,J2),I2),H2),G2),F2),E2)+ IF(ISNA(F2),IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),0,J2),I2),H2),G2),F2)+ IF(ISNA(G2),IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),0,J2),I2),H2),G2)+ IF(ISNA(H2),IF(ISNA(I2),IF(ISNA(J2),0,J2),I2),H2)))

 

[Jonmo1]

Mayebe this will help

Public Function SumNth(MyRange As Range, Optional x As Long = 1)
Dim c As Range, MyCount As Long, MySum As Double
For Each c In MyRange 'this is a loop that evaluates each cell in the specified range one at a time
    If IsNumeric(c) Then 'This tests if that cell is a Number or not
        MyCount = MyCount + 1 'This counts how many Numbers have been found
        MySum = MySum + c 'If it was a number, it is summed
        If MyCount = x Then Exit For 'This tells it to stop the loop when the count of numbers is 6
    End If
Next c 'This makes the code repeat back to the For line.
SumNth = MySum 'This returns the Sum to the formula cell
End Function

 

[Peter_SSs]

If you wanted a solution with standard Excel functions, and given that you have stated that you are using Excel 2010, then you could try:

=IFERROR(AGGREGATE(9,6,(C2:INDEX(C2:AA2,AGGREGATE(15,6,COLUMN(C2:AA2)/ISNUMBER(C2:AA2),MIN(6,COUNT(C2:AA2)))-COLUMN(C2)+1))),0)