Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another SUMPRODUCT Query | Excel Worksheet Functions | |||
Sumproduct query | Excel Discussion (Misc queries) | |||
SUMPRODUCT IF query | Excel Discussion (Misc queries) | |||
SUMPRODUCT Query | Excel Discussion (Misc queries) | |||
I think its a sumproduct query? | Excel Discussion (Misc queries) |