sumproduct query
 

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?

sumproduct query
 

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)

"PBcorn" wrote in message
...
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?

sumproduct query
 

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!
================================================== ===

"PBcorn" wrote in message
...
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?

sumproduct query
 

You don't need any SUMPRODUCTs.

See MMULT in Excel Help
--
Gary''s Student - gsnu200773


"PBcorn" wrote:

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?

sumproduct query
 

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.
--
David Biddulph

"PBcorn" wrote in message
...
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?

sumproduct query
 

"Ron Coderre" wrote:

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

sumproduct query
 

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)

"PBcorn" wrote in message
...


"Ron Coderre" wrote:

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