Sum of RangeA by RangeB

[Sara]

Hi

Table example of my problem:

|A |B |C |D |E
1 |Price |10 |20 |30
2 |Qty |1 |2 |3 |=SUM(C3:E3*C4:E4)

In E2, I get a "#VALUE!" error, however when I click in
the function table (the tick box next to the address bar)
it shows my answer of 140.

Does anyone know what the problem is or how it can be
fixed?

I have Excel 2000 (SR1). My friend has done the same
table, but has got the answer showing in E2 and I don't
know what ver. of Excel he has. I am working with hundreds
of cells, so it's not as easy to dismiss

Please help

TIA
Sara

 

[Kevin Stecyk]

Sara,

Very close. Just go "control shift enter" when you enter your equation.
You want an array entered equation and hitting those three keys will create
the equation for you.

Regards,
Kevin

 

[sara]

Sorry, that formula is supposed to be:
=SUM(B11*B22)

Sara

 

[Kevin Stecyk]

Sara,

=SUM(B11*B22)

Then control shift enter.

AFTERWARDS, it should look like

{=SUM(B11*B22)}

But don't enter the funny brackets. Excel places them automatically after
you hit control shift enter.

Regards,
Kevin

 

[Sara]

Thank you very much Kevin!

It worked like a bought one!

Kind regards,
Sara

 

[Harlan Grove]

Sorry, that formula is supposed to be:
=SUM(B11*B22)

....

You should consider using SUMPRODUCT(B11,B22) instead. For something
like this SUMPRODUCT recalculates faster than SUM(B11*B22), and it
doesn't need to be entered as an array formula.

 

[Sara]

Excellent! Thank you!
It's much more efficient!

Sara