formula help please
 

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--


Gary

formula help please
 

=SUM(B4:R4*TRANSPOSE(K3:K19))

array entered.

"Gary Keramidas" wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--


Gary

formula help please
 

Correction:
=SUM(B4:R4*TRANSPOSE(Production!K3:K19))


"Gary Keramidas" wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--


Gary

formula help please
 

one other thing, there will not be values in every column of row 4 in this
example, so only columns with numbers will factor into the total.
in this example, there are only numbers in D4 and F4.

not sure if this makes any difference, though.
--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--


Gary

formula help please
 

One way:

=SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Gary Keramidas wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--

Gary

--

Dave Peterson

formula help please
 

Or just

=MMULT(B4:R4,Production!$K$3:$K$19)

(not array entered)

Dave Peterson wrote:

One way:

=SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Gary Keramidas wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--

Gary

--

Dave Peterson

--

Dave Peterson

formula help please
 

i put the values from K3:K19 in b1:r1 on the same sheet and this seemed to work.

which solution is best?

=SUMPRODUCT((B4:R40)*($B$1:$R$1)*($B4:$R4))

--


Gary


"Dave Peterson" wrote in message
...
One way:

=SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Gary Keramidas wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--

Gary

--

Dave Peterson

formula help please
 

this one gave me a #value for some reason. i'll use the other option. thanks for
the transpose idea.

--


Gary


"Dave Peterson" wrote in message
...
Or just

=MMULT(B4:R4,Production!$K$3:$K$19)

(not array entered)

Dave Peterson wrote:

One way:

=SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Gary Keramidas wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--

Gary

--

Dave Peterson

--

Dave Peterson

formula help please
 

thanks, jmb, worked fine. i think i'll just copy the values to the same sheet
and use the =sum array formula. then the user can see what they're multiply by
anyway.



--


Gary


"JMB" wrote in message
...
Correction:
=SUM(B4:R4*TRANSPOSE(Production!K3:K19))


"Gary Keramidas" wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--


Gary

formula help please
 

ok, thanks, that's what i thought might be the case.

--


Gary


"JMB" wrote in message
...
The MMULT error you see is likely due to some of the cells being empty.

Is the 0 intended to test for empty cells or negative values? Sumproduct
will treat empty cells as 0, perhaps just:

=SUMPRODUCT($B$1:$R$1, $B4:$R4)


"Gary Keramidas" wrote:

i put the values from K3:K19 in b1:r1 on the same sheet and this seemed to
work.

which solution is best?

=SUMPRODUCT((B4:R40)*($B$1:$R$1)*($B4:$R4))

--


Gary


"Dave Peterson" wrote in message
...
One way:

=SUM(TRANSPOSE(B4:R4)*Production!$K$3:$K$19)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do
it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Gary Keramidas wrote:

is it possible to shorten this, possibly array or sumproduct?

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Pro duction!$K$5)+(E4*Production!$K$6)+(F4*Production! $K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I 4*Production!$K$10)+(J4*Production!$K$11)+(K4*Prod uction!$K$12)+(L4*Production!$K$13)+(M4*Production !$K$14)+(N4*Production!$K$15)+(O4*Production!$K$16 )+(P4*Production!$K$17)+(Q4*Production!$K$18)+(R4* Production!$K$19)

--

Gary

--

Dave Peterson