formula help please

[Gary Keramidas]

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

=(B4*Production!$K$3)+(C4*Production!$K$4)+(D4*Production!$K$5)+(E4*Production!$K$6)+(F4*Production!$K$7)+(G4*Production!$K$8)+(H4*Production!$K$9)+(I4*Production!$K$10)+(J4*Production!$K$11)+(K4*Production!$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)

 

[JMB]

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

array entered.

 

[JMB]

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

 

[Gary Keramidas]

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.

 

[Dave Peterson]

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.)

 

[Dave Peterson]

Or just

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

(not array entered)

 

[Gary Keramidas]

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:R4>0)*($B$1:$R$1)*($B4:$R4))

 

[Gary Keramidas]

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

 

[Gary Keramidas]

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.

 

[JMB]

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)

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:R4>0)*($B$1:$R$1)*($B4:$R4))

 

[Gary Keramidas]

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

--


Gary

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)