formula help please

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

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

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

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

Gary Keramidas

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

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

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)


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

Gary Keramidas

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top