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

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*$F2 0),($G22*$G20),($H22*$H20),($I22*$I20),($J22*$J20) ,($K22*$K20),($L22*$L20),($M22*$M20),($N22*$N20),( $O22*$O20),($P22*$P20),($Q22*$Q20),($R22*$R20),($S 22*$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)

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

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

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


"Randy" wrote:

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*$F2 0),($G22*$G20),($H22*$H20),($I22*$I20),($J22*$J20) ,($K22*$K20),($L22*$L20),($M22*$M20),($N22*$N20),( $O22*$O20),($P22*$P20),($Q22*$Q20),($R22*$R20),($S 22*$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)

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

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

"Randy" wrote in message
...
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*$F2 0),($G22*$G20),($H22*$H20),($I22*$I20),($J22*$J20) ,($K22*$K20),($L22*$L20),($M22*$M20),($N22*$N20),( $O22*$O20),($P22*$P20),($Q22*$Q20),($R22*$R20),($S 22*$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)

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

This worked great! Thanks to all!!

"T. Valko" wrote:

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

"Randy" wrote in message
...
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*$F2 0),($G22*$G20),($H22*$H20),($I22*$I20),($J22*$J20) ,($K22*$K20),($L22*$L20),($M22*$M20),($N22*$N20),( $O22*$O20),($P22*$P20),($Q22*$Q20),($R22*$R20),($S 22*$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)

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

You're welcome. Thanks for the feedback!

Biff

"Randy" wrote in message
...
This worked great! Thanks to all!!

"T. Valko" wrote:

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

"Randy" wrote in message
...
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*$F2 0),($G22*$G20),($H22*$H20),($I22*$I20),($J22*$J20) ,($K22*$K20),($L22*$L20),($M22*$M20),($N22*$N20),( $O22*$O20),($P22*$P20),($Q22*$Q20),($R22*$R20),($S 22*$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)