To get round the formula length problem I've used 2 helper columns Cell U2 contains the formula =IF(B2="yes",1,0)+IF(AND(B2="yes",C2="yes"),0,IF(AND(B2="no",C2="yes"),1,0))+IF(AND(C2="yes",D2="yes"),0,IF(AND(C2="no",D2="yes"),1,0))+IF(AND(D2="yes",E2="yes"),0,IF(AND(D2="no",E2="yes"),1,0))+IF(AND(E2="yes",F2="yes"),0,IF(AND(E2="no",F2="yes"),1,0))+IF(AND(F2="yes",G2="yes"),0,IF(AND(F2="no",G2="yes"),1,0))+IF(AND(G2="yes",H2="yes"),0,IF(AND(G2="no",H2="yes"),1,0))+IF(AND(H2="yes",I2="yes"),0,IF(AND(H2="no",I2="yes"),1,0))+IF(AND(I2="yes",J2="yes"),0,IF(AND(I2="no",J2="yes"),1,0))+IF(AND(J2="yes",K2="yes"),0,IF(AND(J2="no",K2="yes"),1,0)) Cell V2 contains the formula =IF(AND(L2="yes",M2="yes"),0,IF(AND(L2="no",M2="yes"),1,0))+IF(AND(M2="yes",N2="yes"),0,IF(AND(M2="no",N2="yes"),1,0))+IF(AND(N2="yes",O2="yes"),0,IF(AND(N2="no",O2="yes"),1,0))+IF(AND(O2="yes",P2="yes"),0,IF(AND(O2="no",P2="yes"),1,0))+IF(AND(P2="yes",Q2="yes"),0,IF(AND(P2="no",Q2="yes"),1,0))+IF(AND(Q2="yes",R2="yes"),0,IF(AND(Q2="no",R2="yes"),1,0))+IF(AND(R2="yes",S2="yes"),0,IF(AND(R2="no",S2="yes"),1,0))+IF(AND(S2="yes",T2="yes"),0,IF(AND(S2="no",T2="yes"),1,0)) Cell W2 contains the formula =U2+V2 Trust you get the logic? Lee
-- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
UnpaidWork(2).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
