How can I get around 30 argument limit of SUM function?

R

Randy

I've tried to break this formula up into defined names but it is not working
properly. Does anyone have any suggestions how I can get around the 30
argument limitation of the SUM function? Each argument is multiplying a
number times a percentage.

Thanks!

Randy

=SUM(($C22*$C20),($D22*$D20),($E22*$E20),($F22*$F20),($G22*$G20),($H22*$H20),($I22*$I20),($J22*$J20),($K22*$K20),($L22*$L20),($M22*$M20),($N22*$N20),($O22*$O20),($P22*$P20),($Q22*$Q20),($R22*$R20),($S22*$S20),($T22*$T20),($U22*$U20),($V22*$V20),($W22*$W20),($X22*$X20),($Y22*$Y20),($X22*$X20),($AA22*$AA20),($AB22*$AB20),($AC22*$AC20),($AD22*$AD20),($AE22*$AE20),($AF22*$AF20))/SUM($C20:AF20)
 
D

Duke Carey

Without analyzing your formula overmuch, looks like SUMPRODUCT() would fix
your issue

=SUMproduct($C22:Af22,$C20:AF20)/SUM($C20:AF20)
 
T

T. Valko

Try this:

=SUMPRODUCT($C22:$AF22,$C20:$AF20)/SUM($C20:AF20)

To answer your question about 30 arguments:

Each set of inner ( ) is *1* argument

So:

=SUM((A1,A2,A3,A3,A5)) has 1 argument

=SUM((A1:A10),(B10:B20),(C20:C30)) has 3 arguments

Biff
 
R

Randy

This worked great! Thanks to all!!

T. Valko said:
Try this:

=SUMPRODUCT($C22:$AF22,$C20:$AF20)/SUM($C20:AF20)

To answer your question about 30 arguments:

Each set of inner ( ) is *1* argument

So:

=SUM((A1,A2,A3,A3,A5)) has 1 argument

=SUM((A1:A10),(B10:B20),(C20:C30)) has 3 arguments

Biff
Click to expand...
 
Top