smartie91 Posted June 19, 2008 Share Posted June 19, 2008 (edited) Hi AllI need help with a excel if statement please what i have is a list containing machine ids T003478N000299T003067TT90044all the above are what we call FM only the first to chars should be used as there are over 2000 ids the second digit is a zeroNR02297TX00024TR02175TC12345these are called SFthere for if C1 = T0 then print FM or c1=nr print SFi have this so far =IF((C1="TT"),FM,IF((C1="N0"),FM,IF((C1="NR"),SF,IF((C1="TR"),SF,"NONE"))))and the error i get is #NAME? TT90185NONE T003478#NAME? NR02297NONE TX00024hope this make sence thanks NigeleditI have got it to work if i only put 2 char in c1 useing this =IF((E5="TT"),"FM",IF((E5="N0"),"FM",IF((E5="NR"),"SF",IF((E5="TR"),"SF",IF((E5="T0"),"FM",IF((E5="TX"),"SF","NONE"))))))how do i no only pick first 2 chars also i need altogether 10 if statement i think excel only does 7 is that right thanksnigel Edited June 19, 2008 by smartie91 Link to comment Share on other sites More sharing options...
IcemanND Posted June 19, 2008 Share Posted June 19, 2008 I won't redo the whole line but you should get the idea:=if (Left(C1,2)="TT","FM",if(Left(C1,2)="NR","SF","None")) Link to comment Share on other sites More sharing options...
smartie91 Posted June 20, 2008 Author Share Posted June 20, 2008 Thanks IcemanNDI have this now=IF(LEFT(G4,1)="F","CYLYPSO",IF(LEFT(G4,2)="TX","SF",IF(LEFT(G4,2)="N0","FM",IF(LEFT(G4,2)="TT","FM",IF(LEFT(G4,2)="T0","FM",IF(LEFT(G4,2)="TR","SF",IF(LEFT(G4,2)="NR","SF","CCM")))))))which works fine But the problem is now i cant search from this column A ..... B .......C.........D.......... E......... FSF 753651 Address Name 2A TX00085FM 787621 Address Name 2B NR03955CCM 792373 Address Name 2C NR03024SF 794187 Address Name 3B NR00127Column A is the result of above I need to display how many SF,FM,CCM, there are in 2A,2B.2C.thanks for your helpNigel Link to comment Share on other sites More sharing options...
jaclaz Posted June 21, 2008 Share Posted June 21, 2008 Wouldn't a "count if" function do?http://www.ozgrid.com/Excel/count-if.htmjaclaz Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now