Excel - Sumproduct across mutliple columns
Asked By JANA
24-Jan-10 10:52 PM
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,c1: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 do not want to add 20 individual sumproduct
formulas.
Thanks in advance!
Jana
Microsoft Excel
(1)
SUMPRODUCT
(1)
Sums
(1)
Fred Smith replied to JANA
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
Jacob Skaria replied to JANA
Try
=SUMPRODUCT(A1:A4*B1:F4)
--
Jacob
T. Valko replied to JANA
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
Filter in Microsoft Excel Excel Hi all, I am using microsoft excel 2000. I need help in filtering the data. I have two columns as below Name follows. b How we will define this filter. Please help me. . . . Thanx for reading. Robin Excel Worksheet Discussions Microsoft Excel (1) SUMPRODUCT (1) COUNTIF (1) OFFSET (1) COLUMN (1) VBA (1) À´ªàµà´¤àµ
Sumproduct Problem Excel = SUMPRODUCT(- -(M21:M5127 = "a")*(N21:N5127 = "f")*(L21:L5127 = "FALSE")) This formula does not work, but I think it should. Any suggestions? Thanks Excel Discussions Microsoft Excel (1) SUMPRODUCT (1) TEXT (1) Booleans (1) Insead (1) Are those Booleans in L21:L5127? If so you need to remove the quotes. Try one of these. . . For Boolean FALSE: = SUMPRODUCT(- -(M21:M5127 = "a"), - -(N21:N5127 = "f"), - -(L21:L5127 = FALSE)) For TEXT false: = SUMPRODUCT(- -(M21:M5127
Help with Excel Excel I have a spreadsheet with 3 columns - I want to know the number of times and col b = textb and col c = text c. how do I do this in excel? Excel Miscellaneous Discussions Microsoft Excel (1) Excel 2007 (1) Excel (1) SUMPRODUCT (1) COUNTIF (1) Bliengme (1) Times (1) Texta (1) = sumproduct(- -(a1:a10 = "texta
Embed a 'match' statement in sumproduct? Excel Excel 2007 I am trying to help a coworker who wants to generate a few summary List of invalid product codes This formula works, but does not exclude the invalid products: = SUMPRODUCT(('Sheet 2'!$C$2:$C$60000 = 'Sheet 1'!$F7)*1, ('Sheet 2'!$I$2:$I I try to add a match statement, to exclude invalid products (multiply matches by zero): = SUMPRODUCT(('Sheet 2'!$C$2:$C$60000 = 'Sheet 1'!$F7)*1, ('Sheet 2'!$I$2:$I 0)*1) I cannot tell if there is a problem in my syntax, or if Sumproduct automatically thinks my match statement should be rows 2-60000 instead of 1-100, which it is not working how I'd expect. I welcome any suggestions! Thank you, Keith Excel Worksheet Discussions Microsoft Excel (1) Excel 2007 (1) SUMPRODUCT (1) ISERROR (1) MATCH (1) ISNA (1) Lifesavers (1
countif two corresponding cells meet the criteria Excel how do you countif . . . if range 1 = "A" and range 2 = "B" Excel Worksheet Discussions Microsoft Excel (1) Excel 2007 (1) Excel (1) SUMPRODUCT (1) COUNTIFS (1) Try one of these. All versions of Excel: = SUMPRODUCT(- -(A1:A10 = "A"), - -(B1:B10 = "B")) Excel 2007 only: = COUNTIFS(A1:A10, "A", B1