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)

> 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?

Reply to this Message

PBcorn - 12 Mar 2008 17:11 GMT

> With these 2 cell ranges
>
[quoted text clipped - 15 lines]
> 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

Reply to this Message

Ron Coderre - 12 Mar 2008 17:23 GMT

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)

>> With these 2 cell ranges
>>
[quoted text clipped - 39 lines]
>
> Many thanks

Reply to this Message

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.

Signature

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?

Reply to this Message

You don't need any SUMPRODUCTs.

See MMULT in Excel Help

Signature

Gary''s Student - gsnu200773

> 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?

Reply to this Message

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.

Signature

David Biddulph

> 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?

Reply to this Message