Formula Too Long

[Lester601]

I have a formula that is twice the size of the one below. However, when I try to add more to it I get a message that says formula to long. Does anyone know away around this? Here is the formula that I currently have
=IF($AJ11<>"",Analysis!$P$14,0)+IF($AK11<>"",Analysis!$P$15,0)+IF($AL11<>"",Analysis!$P$16,0)+IF($AM11<>"",Analysis!$P$17,0)+IF($AN11<>"",Analysis!$P$18,0)+IF($AO11<>"",Analysis!$P$19,0)+IF($AP11<>"",Analysis!$P$20,0)+IF($AQ11<>"",Analysis!$P$21,0)+IF($AR11<>"",Analysis!$P$22,0)+IF($AS11<>"",Analysis!$P$23,0)+IF($AT11<>"",Analysis!$P$24,0)+IF($AU11<>"",Analysis!$P$25,0)+IF($AV11<>"",Analysis!$P$26,0)+IF($AW11<>"",Analysis!$P$27,0)+IF($AX11<>"",Analysis!$P$28,0)+IF($AY11<>"",Analysis!$P$29,0)+IF($AZ11<>"",Analysis!$P$30,0)+IF($BA11<>"",Analysis!$P$31,0)+IF($BB11<>"",Analysis!$P$32,0)+IF($BC11<>"",Analysis!$P$33,0)+IF($BD11<>"",Analysis!$P$34,0)+IF($BE11<>"",Analysis!$P$35,0)+IF($BF11<>"",Analysis!$P$36,0)+IF($BG11<>"",Analysis!$P$37,0)+IF($BH11<>"",Analysis!$P$38,0)+IF($BI11<>"",Analysis!$P$39,0)+IF($BJ11<>"",Analysis!$P$40,0)+IF($BK11<>"",Analysis!$P$41,0)+IF($BL11<>"",Analysis!$P$42,0)+IF($BM11<>"",Analysis!$P$43,0)+IF($BN11<>"",Analysis!$P$44,0)+IF($BO11<>"",Analysis!$P$45,0)+IF($BP11<>"",Analysis!$P$46,0)

 

[NateO]

Hi Lester,

After a quick review, I think you should just add the cells in question. Your conditions all look like:

if cell isn't blank, add cells contents, otherwise add zero.

But, if they're blank, Excel will simply add zero.

Otherwise you might want to explain the logic of your tests (goal set, situation (errors, etc...).

 

[Aladin Akyurek]

Agree with Nate (and Excel) on the unprocessable length of your formula...

Try

=SUMPRODUCT(($AJ$11:$BP$11<>"")*Analysis!$P$14:$P$46)

instead.

 

[Lester601]

I didn't write this code, can you give me an example..

 

[Lester601]

tHANKS FOR THE EXAMPLE GUYS, i WILL TRY IT LATER