sumproduct query

[PBcorn]

Probably a simple query - I wish to use 6 sumproduct formulae to multiply
each row of a 7x6 array by one single 1x6 array. Is this possible without
typing out each ofthe 6 sumproduct formulae individually, or expanding the
1x6 array to a 7x6 array?

 

[Ron Coderre]

With these 2 cell ranges

A1:F7 contains numbers
H1:H6 contains numbers

This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER)
transposes H1:H6 from vertical to horizontal and multiplies A1:F7
by those values:
=SUMPRODUCT(A1:F7*TRANSPOSE(H1:H6))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[RagDyeR]

Just use absolute references where necessary.

=SUMPRODUCT(A1:G1,$A$17:$G$17)

Copying this down produces:

=SUMPRODUCT(A2:G2,$A$17:$G$17)
=SUMPRODUCT(A3:G3,$A$17:$G$17)
=SUMPRODUCT(A4:G4,$A$17:$G$17)
.... etc.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Probably a simple query - I wish to use 6 sumproduct formulae to multiply
each row of a 7x6 array by one single 1x6 array. Is this possible without
typing out each ofthe 6 sumproduct formulae individually, or expanding the
1x6 array to a 7x6 array?

 

[Gary''s Student]

You don't need any SUMPRODUCTs.

See MMULT in Excel Help

 

[David Biddulph]

Probably.
Tell us what formulae you are trying to achieve in which cells, and
hopefully someone will be able to tell you how to do it without retyping
each independently.

 

[PBcorn]

With these 2 cell ranges

A1:F7 contains numbers
H1:H6 contains numbers

This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER)
transposes H1:H6 from vertical to horizontal and multiplies A1:F7
by those values:
=SUMPRODUCT(A1:F7*TRANSPOSE(H1:H6))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

thanks, but not sure this is what I need. Sorry - should have posted the
below in the first instance :

product cs1 cs2 cs3 cs4 cs5 cs6 total
a 4 3 4 3 12 5 139
b 5 2 5 2 3 6
c 3 3 6 4 5 6
d 5 4 4 23 7 5
e 2 4 7 44 6 4
f 3.4 5 6 56 6 5
g 3 6 56 44 6 5



cost/unit cs1 cs2 cs2 cs3 cs4 cs5
2 3 6 3 7 1

effectively i wish to fill the formula giving the figure of 139
:=SUMPRODUCT(D10:I10,D21:I21)

down but without changing the second array argument so that cost/unit is
applied to each row in the top table.

Many thanks

 

[Ron Coderre]

Here you go.....
Try this:
J10: =SUMPRODUCT(D10:I10,D$21:I$21)

Copy that formula down through J16

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)