sumproduct function calculating wrong?

donalde · Mar 6th, 2002, 06:18 AM

I create new sheet, format all cell to be number type. Then I paste data from another sheet, using paste special -> values
A B
15973,26 1,00
318,10 18,00
98,94 8,00
251,09 1,00
276,68 4,00
column A has value, column B has multiplier for value. When I count A1*B1+A4*B4+A5*A6 I get 17331,07 which is correct. But when I use =SUMPRODUCT(A1:B5) I get value 16950,7 which is impossible value. Correct value would be 23848,30. Does somebody have clue where this goes wrong? Same error happens when using general type cells. Desimal
separator is , (european style)

Nobby · Mar 6th, 2002, 06:31 AM

Based on the data you posted, the sums you are getting are correct.

Your answer of 16950.07 is the sum of:
A1+B1+A2+B2+A3+B3+A4+B4+A5+B5

Nobby

Aladin Akyurek · Mar 6th, 2002, 06:41 AM

Quote:

On 2002-03-06 05:18, donalde wrote:
I create new sheet, format all cell to be number type. Then I paste data from another sheet, using paste special -> values
A B
15973,26 1,00
318,10 18,00
98,94 8,00
251,09 1,00
276,68 4,00
column A has value, column B has multiplier for value. When I count A1*B1+A4*B4+A5*A6 I get 17331,07 which is correct. But when I use =SUMPRODUCT(A1:B5) I get value 16950,7 which is impossible value. Correct value would be 23848,30. Does somebody have clue where this goes wrong? Same error happens when using general type cells. Desimal
separator is , (european style)

It's:

=SUMPRODUCT((A1:A5)*(B1:B5))

Mark W. · Mar 6th, 2002, 08:03 AM

Use...

=SUMPRODUCT(A1:A5,B1:B5)

donalde · Mar 6th, 2002, 11:53 PM

Many thank to all who replied, I have made stupid mistake on syntax. Mark W, thanks for giving correct syntax.

Aladin Akyurek · Mar 6th, 2002, 11:58 PM

Quote:

On 2002-03-06 22:53, donalde wrote:
Many thank to all who replied, I have made stupid mistake on syntax. Mark W, thanks for giving correct syntax.

=SUMPRODUCT((A1:A5)*(B1:B5))

is also correct syntaxwise.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 06:18 AM	#1
donalde Board Regular Join Date: Feb 2002 Location: Finland Posts: 72	I create new sheet, format all cell to be number type. Then I paste data from another sheet, using paste special -> values A B 15973,26 1,00 318,10 18,00 98,94 8,00 251,09 1,00 276,68 4,00 column A has value, column B has multiplier for value. When I count A1B1+A4B4+A5*A6 I get 17331,07 which is correct. But when I use =SUMPRODUCT(A1:B5) I get value 16950,7 which is impossible value. Correct value would be 23848,30. Does somebody have clue where this goes wrong? Same error happens when using general type cells. Desimal separator is , (european style)

Mar 6th, 2002, 06:31 AM	#2
Nobby Board Regular Join Date: Feb 2002 Location: United Kingdom Posts: 68	Based on the data you posted, the sums you are getting are correct. Your answer of 16950.07 is the sum of: A1+B1+A2+B2+A3+B3+A4+B4+A5+B5 Nobby

Mar 6th, 2002, 08:03 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Use... =SUMPRODUCT(A1:A5,B1:B5)

Mar 6th, 2002, 11:53 PM	#5
donalde Board Regular Join Date: Feb 2002 Location: Finland Posts: 72	Many thank to all who replied, I have made stupid mistake on syntax. Mark W, thanks for giving correct syntax.