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]

Welcome to the board.

Try

=SUMIF($C2:INDEX($C2:$AB2,6+COUNTIF($C2:$AB2,"#N/A")),"<>#N/A")

 

[Rick Rothstein]

Try

=SUMIF($C2:INDEX($C2:$AB2,6+COUNTIF($C2:$AB2,"#N/A")),"<>#N/A")

I gave you a "Like" just because you actually sat down and tried (and succeeded!!) to decipher that formula. Kudos to you for doing that!

 

[Jonmo1]

I gave you a "Like" just because you actually sat down and tried (and succeeded!!) to decipher that formula. Kudos to you for doing that!

Thanks.

But I didn't really decipher the 'formula'.
Rather, I deciphered what the OP said was wanted.
I barely even looked at the formula, just to get an idea of the cell references.

 

[Rick Rothstein]

But I didn't really decipher the 'formula'.
Rather, I deciphered what the OP said was wanted.
I barely even looked at the formula, just to get an idea of the cell references.

It doesn't matter... I saw that formula and was mesmerized to the point of distraction... I didn't even notice the description!

 

[andyball2311]

Thanks for the response but that didn't work. More than half of the rows returned a REF error and many more gave the incorrect value.
[garbled picture code removed]
The years go across to 2014 in column AA. Again I want to start with the first actual value and add across until I have 6 total (but not more) years of data. If there are less than 6, I want all returned, and if there is none I want a zero.

 

[Jonmo1]

Is it fair to say that ALL cells in the row from C to AA are populated with either a number or #N/A error ?
There are no blanks or text strings in any of them?

 

[andyball2311]

Correct. Every cell either has a N/A or a value ranging from -20.0 to 20.0.

 

[Jonmo1]

Here's a VBA solution.

Put this code into a new module (Insert - Module)
Paste the following code

Public Function SumNth(MyRange As Range, x As Long)
Dim c As Range, MyCount As Long, MySum As Double
For Each c In MyRange
    If IsNumeric(c) Then
        MyCount = MyCount + 1
        MySum = MySum + c
        If MyCount = x Then Exit For
    End If
Next c
SumNth = MySum
End Function

Now use it in a formula as
=SumNth(C2:AA2,6)

 

[andyball2311]

That appears to have worked perfectly so thank you a ton.

By no means do you have to, but as one final request can you explain the VBA code above? I'm just learning VBA and hopefully in the future I can do these myself. Either way thank you!