formula help please

  • Thread starter Thread starter Gary Keramidas
  • Start date Start date
G

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)
 
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.
 
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.)
 
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))
 
this one gave me a #value for some reason. i'll use the other option. thanks for
the transpose idea.
 
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.
 
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 said:
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))
Click to expand...
 
ok, thanks, that's what i thought might be the case.

--


Gary


JMB said:
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)
Click to expand...
 
Back
Top