simple math question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Emerald Saint - 01 Nov 2007 05:24 GMT

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.

Reply to this Message

T. Valko - 01 Nov 2007 05:49 GMT

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)

Signature

Biff
Microsoft Excel MVP

> My Excel is a rather old version but I think the answer to this question
> would be the same regardless of version.
[quoted text clipped - 19 lines]
>
> TIA Bill S.

Reply to this Message

Peo Sjoblom - 01 Nov 2007 05:52 GMT

=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

Signature

Regards,

Peo Sjoblom

> My Excel is a rather old version but I think the answer to this question
> would be the same regardless of version.
[quoted text clipped - 19 lines]
>
> TIA Bill S.

Reply to this Message

Emerald Saint - 01 Nov 2007 06:31 GMT

Peo & Biff - thanks for the help

Bill S.

> =SUMPRODUCT(A1:A4,B1:B4)
>
[quoted text clipped - 24 lines]
>>
>> TIA Bill S.

Reply to this Message