Forumla too large

[paulcraigdainty]

I've just put the following forumla into the cell of an Excel sheet however I got the error 'Forumla too large'. Is there any way I can reduce the size of the syntax? I've been looking at nested forumlas but can't work out if this is what i need.


=IF(B3=B89,BW3,"0")+IF(B4=B89,BW4,"0")+IF(B5=B89,BW5,"0")+IF(B6=B89,BW6,"0")+IF(B7=B89,BW7,"0")+IF(B8=B89,BW8,"0")+IF(B9=B89,BW9,"0")+IF(B10=B89,BW10,"0")+IF(B11=B89,BW11,"0")+IF(B12=B89,BW12,"0")+IF(B13=B89,BW13,"0")+IF(B14=B89,BW14,"0")+IF(B15=B89,BW15,"0")+IF(B16=B89,BW16,"0")+IF(B17=B89,BW17,"0")+IF(B18=B89,BW18,"0")+IF(B19=B89,BW19,"0")+IF(B20=B89,BW20,"0")+IF(B21=B89,BW21,"0")+IF(B22=B89,BW22,"0")+IF(B23=B89,BW23,"0")+IF(B24=B89,BW24,"0")+IF(B25=B89,BW25,"0")+IF(B26=B89,BW26,"0")+IF(B27=B89,BW27,"0")+IF(B28=B89,BW28,"0")+IF(B29=B89,BW29,"0")+IF(B30=B89,BW30,"0")+IF(B31=B89,BW31,"0")+IF(B32=B89,BW32,"0")+IF(B33=B89,BW33,"0")+IF(B34=B89,BW34,"0")+IF(B35=B89,BW35,"0")+IF(B36=B89,BW36,"0")+IF(B37=B89,BW37,"0")+IF(B38=B89,BW38,"0")+IF(B39=B89,BW39,"0")+IF(B40=B89,BW40,"0")+IF(B41=B89,BW41,"0")+IF(B42=B89,BW42,"0")+IF(B43=B89,BW43,"0")+IF(B44=B89,BW44,"0")+IF(B45=B89,BW45,"0")+IF(B46=B89,BW46,"0")+IF(B47=B89,BW47,"0")+IF(B48=B89,BW48,"0")+IF(B49=B89,BW49,"0")+IF(B50=B89,BW50,"0")

 

[qafself]

Hi Paul,

Could you post your sheet, or a part of it, with an explanation of what you want to achieve?

Ed

 

[KenHigg]

I would add a column and put the formula for each row there. Then sum the column.

???

 

[KenHigg]

Sorry Ed, didn't mean to butt in

 

[qafself]

No probs, Ken.

Howabout

=SUMIF(B3:B50,B59,BW3:BW50)

Ed

 

[paulcraigdainty]

Hi,

The attached sheet is a scaled down version and does what what I want. However it seems to be restricted to a certain formula size. When I try and use the formula originally posted it says it's too big.

Basically I want the formula to add the values associated with specific teams.

 

[qafself]

Hi Paul,

Did you try the formula I posted ealier? It should do the trick

=SUMIF($B$3:$B$50,B59,$BW$3:$BW$50)

And copy down

Edited to put in absolute refs