Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across mutliple columns
I need to apply a sumproduct formula across multiple columns. See example
below: A B C D E F 1 $50 100 120 100 140 150 2 $60 140 90 100 160 140 3 $70 100 50 60 70 80 4 $80 10 100 120 140 200 I need a simpler formula that will give me one total for the following: sumproduct($A$1:$A$4,B1:B4)+sumproduct($A$1:$A$4,c 1:c4)+sumproduct($A$1:$A$4,d1:d4)+sumproduct($A$1: $A$4,e1:e4)+sumproduct($A$1:$A$4,f1:f4). I have 20 columns across and don't want to add 20 individual sumproduct formulas. Thanks in advance! Jana |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across mutliple columns
What I would do add a column which sums your 20 columns. Then calculate your
Sumproduct, as in: =sumproduct(a1:a4,t1:t4) Regards, Fred "JANA" wrote in message ... I need to apply a sumproduct formula across multiple columns. See example below: A B C D E F 1 $50 100 120 100 140 150 2 $60 140 90 100 160 140 3 $70 100 50 60 70 80 4 $80 10 100 120 140 200 I need a simpler formula that will give me one total for the following: sumproduct($A$1:$A$4,B1:B4)+sumproduct($A$1:$A$4,c 1:c4)+sumproduct($A$1:$A$4,d1:d4)+sumproduct($A$1: $A$4,e1:e4)+sumproduct($A$1:$A$4,f1:f4). I have 20 columns across and don't want to add 20 individual sumproduct formulas. Thanks in advance! Jana |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across mutliple columns
Try
=SUMPRODUCT(A1:A4*B1:F4) -- Jacob "JANA" wrote: I need to apply a sumproduct formula across multiple columns. See example below: A B C D E F 1 $50 100 120 100 140 150 2 $60 140 90 100 160 140 3 $70 100 50 60 70 80 4 $80 10 100 120 140 200 I need a simpler formula that will give me one total for the following: sumproduct($A$1:$A$4,B1:B4)+sumproduct($A$1:$A$4,c 1:c4)+sumproduct($A$1:$A$4,d1:d4)+sumproduct($A$1: $A$4,e1:e4)+sumproduct($A$1:$A$4,f1:f4). I have 20 columns across and don't want to add 20 individual sumproduct formulas. Thanks in advance! Jana |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across mutliple columns
sumproduct($A$1:$A$4,B1:B4)+sumproduct($A$1:$A$4, c1:c4)+sumproduct($A$1:$A$4,d1:d4)+sumproduct($A$1 :$A$4,e1:e4)+sumproduct($A$1:$A$4,f1:f4)
This will do the same thing as long as there is no text in the range. =SUMPRODUCT(A1:A4*B1:F4) -- Biff Microsoft Excel MVP "JANA" wrote in message ... I need to apply a sumproduct formula across multiple columns. See example below: A B C D E F 1 $50 100 120 100 140 150 2 $60 140 90 100 160 140 3 $70 100 50 60 70 80 4 $80 10 100 120 140 200 I need a simpler formula that will give me one total for the following: sumproduct($A$1:$A$4,B1:B4)+sumproduct($A$1:$A$4,c 1:c4)+sumproduct($A$1:$A$4,d1:d4)+sumproduct($A$1: $A$4,e1:e4)+sumproduct($A$1:$A$4,f1:f4). I have 20 columns across and don't want to add 20 individual sumproduct formulas. Thanks in advance! Jana |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mutliple Sumproduct criteria | Excel Worksheet Functions | |||
mutliple criteria vlookups | Excel Worksheet Functions | |||
Selecting mutliple ranges | Excel Worksheet Functions | |||
mutliple conditions | Excel Discussion (Misc queries) | |||
how to merge mutliple columns vertically | Excel Worksheet Functions |