Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mutliple Sumproduct criteria PJFry Excel Worksheet Functions 1 July 24th 09 01:08 AM
mutliple criteria vlookups Squid Excel Worksheet Functions 5 December 10th 08 11:06 PM
Selecting mutliple ranges Scott Graft Excel Worksheet Functions 4 June 13th 08 08:36 PM
mutliple conditions Sue Excel Discussion (Misc queries) 1 May 2nd 08 10:13 PM
how to merge mutliple columns vertically techteacher Excel Worksheet Functions 0 March 10th 05 03:37 AM


All times are GMT +1. The time now is 11:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"