# How do I simplify this if statement to just add 1 every time (and possibly go with no limit)?

``````=if(C24 ='Ability Base'!\$A\$2;sum('Ability Base'!\$B\$2);
if(C24 ='Ability Base'!\$A\$3;sum('Ability Base'!\$B\$3);
if(C24 ='Ability Base'!\$A\$4;sum('Ability Base'!\$B\$4);
if(C24 ='Ability Base'!\$A\$5;sum('Ability Base'!\$B\$5);
if(C24 ='Ability Base'!\$A\$6;sum('Ability Base'!\$B\$6);
if(C24 ='Ability Base'!\$A\$7;sum('Ability Base'!\$B\$7);
if(C24 ='Ability Base'!\$A\$8;sum('Ability Base'!\$B\$8);
if(C24 ='Ability Base'!\$A\$9;sum('Ability Base'!\$B\$9);
if(C24 ='Ability Base'!\$A\$10;sum('Ability Base'!\$B\$10);
if(C24 ='Ability Base'!\$A\$11;sum('Ability Base'!\$B\$11);
if(C24 ='Ability Base'!\$A\$12;sum('Ability Base'!\$B\$12);
if(C24 ='Ability Base'!\$A\$13;sum('Ability Base'!\$B\$13);
if(C24 ='Ability Base'!\$A\$14;sum('Ability Base'!\$B\$14);
if(C24 ='Ability Base'!\$A\$15;sum('Ability Base'!\$B\$15);
if(C24 ='Ability Base'!\$A\$16;sum('Ability Base'!\$B\$16);
if(C24 ='Ability Base'!\$A\$17;sum('Ability Base'!\$B\$17);
if(C24 ='Ability Base'!\$A\$18;sum('Ability Base'!\$B\$18);
if(C24 ='Ability Base'!\$A\$19;sum('Ability Base'!\$B\$19);
if(C24 ='Ability Base'!\$A\$20;sum('Ability Base'!\$B\$20);
if(C24 ='Ability Base'!\$A\$21;sum('Ability Base'!\$B\$21);
if(C24 ='Ability Base'!\$A\$22;sum('Ability Base'!\$B\$22);
if(C24 ='Ability Base'!\$A\$23;sum('Ability Base'!\$B\$23);
if(C24 ='Ability Base'!\$A\$24;sum('Ability Base'!\$B\$24);
if(C24 ='Ability Base'!\$A\$25;sum('Ability Base'!\$B\$25);
if(C24 ='Ability Base'!\$A\$26;sum('Ability Base'!\$B\$26);
if(C24 ='Ability Base'!\$A\$27;sum('Ability Base'!\$B\$27);
if(C24 ='Ability Base'!\$A\$28;sum('Ability Base'!\$B\$28);
if(C24 ='Ability Base'!\$A\$29;sum('Ability Base'!\$B\$29);
if(C24 ='Ability Base'!\$A\$30;sum('Ability Base'!\$B\$30);
if(C24 ='Ability Base'!\$A\$31;sum('Ability Base'!\$B\$31);
if(C24 ='Ability Base'!\$A\$32;sum('Ability Base'!\$B\$32);
if(C24 ='Ability Base'!\$A\$33;sum('Ability Base'!\$B\$33);
if(C24 ='Ability Base'!\$A\$34;sum('Ability Base'!\$B\$34);
if(C24 ='Ability Base'!\$A\$35;sum('Ability Base'!\$B\$35);
if(C24 ='Ability Base'!\$A\$36;sum('Ability Base'!\$B\$36);
if(C24 ='Ability Base'!\$A\$37;sum('Ability Base'!\$B\$37);
if(C24 ='Ability Base'!\$A\$38;sum('Ability Base'!\$B\$38);
if(C24 ='Ability Base'!\$A\$39;sum('Ability Base'!\$B\$39);
if(C24 ='Ability Base'!\$A\$40;sum('Ability Base'!\$B\$40);
if(C24 ='Ability Base'!\$A\$41;sum('Ability Base'!\$B\$41);
if(C24 ='Ability Base'!\$A\$42;sum('Ability Base'!\$B\$42);
if(C24 ='Ability Base'!\$A\$43;sum('Ability Base'!\$B\$43);
if(C24 ='Ability Base'!\$A\$44;sum('Ability Base'!\$B\$44);
if(C24 ='Ability Base'!\$A\$45;sum('Ability Base'!\$B\$45);
if(C24 ='Ability Base'!\$A\$46;sum('Ability Base'!\$B\$46);
if(C24 ='Ability Base'!\$A\$47;sum('Ability Base'!\$B\$47);
if(C24 ='Ability Base'!\$A\$48;sum('Ability Base'!\$B\$48);
if(C24 ='Ability Base'!\$A\$49;sum('Ability Base'!\$B\$49);
if(C24 ='Ability Base'!\$A\$50;sum('Ability Base'!\$B\$50);
"Not Ability")))
``````
-
what is the reason of summing 1 cell? – wilson Oct 8 '10 at 3:36

## 1 Answer

This is the simplified version of the formula you provided in the question:

``````=IF(ISNA(VLOOKUP(C24,'Ability Base'!A\$2:B\$50,2,FALSE)),
"Not Ability",
VLOOKUP(C24,'Ability Base'!A\$2:B\$50,2,FALSE)
)
``````

Maybe you can tell your requirement in words if the above is not suitable.

(Tested in Excel 2003)

-