simple math question

[Emerald Saint]

My Excel is a rather old version but I think the answer to this question
would be the same regardless of version.

I have two columns of 4 numbers each.
Let's call them A1:A4 and B1:B4.
The cell format is simply 'number'.
I want a formula in another cell that calculates the following:
A1*B1+A2*B2+A3*B3+A4*B4
but I want to use a SUM operator and cell range 'colon' operator
instead of typing everything explicitly

something like
SUM((A1:A4)*(B1:B4)) or
SUM(A1*B1:A4*B4)
I tried several variations but none was correct.
I looked around in HELP but they don't show how to do math
with cell ranges

Mostly I got #VALUE! in the cell.

Please help.

TIA Bill S.

 

[T. Valko]

Try this one first:

=SUMPRODUCT(A1:A4,B1:B4)

something like
SUM((A1:A4)*(B1:B4))

It would be like this entered as an array** :

=SUM(A1:A4*B1:B4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Peo Sjoblom]

=SUMPRODUCT(A1:A4,B1:B4)

note that if you get value errors in the other formula you had it indicates
you have numbers that are text

 

[Emerald Saint]

Peo & Biff - thanks for the help

Bill S.

=SUMPRODUCT(A1:A4,B1:B4)

note that if you get value errors in the other formula you had it
indicates you have numbers that are text


--

Regards,

Peo Sjoblom