Hi all,

Another relatively simple one I'm sure, but how would I get the sum of two
columns, where each cell changes and I need the product of each?

EG
A B
1 250 (250)
4 300 (1200)
3 288 (864)
7 133 (931)
2 621 (1242)
Total 4487
And then at the bottom of column B I need the sum of the products of the two
columns.

I thought something like =SUM((A1*B1):(A5*B5)) would work but to no avail.

So if you've got the time I'd appreciate the help.

Thanks,
Mike

"mike_vr" wrote in message
...
Hi all,

Another relatively simple one I'm sure, but how would I get the sum of two
columns, where each cell changes and I need the product of each?

EG
A B
1 250 (250)
4 300 (1200)
3 288 (864)
7 133 (931)
2 621 (1242)
Total 4487
And then at the bottom of column B I need the sum of the products of the
two
columns.

I thought something like =SUM((A1*B1):(A5*B5)) would work but to no
avail.

So if you've got the time I'd appreciate the help.

Thanks,
Mike


The formula you need is
=SUM(A1:A5*B1:B5)
The essential point, however, is that this is an array formula. When you
type it in, don't click ENTER. Instead hold down CTRL and SHIFT whilst
clicking ENTER. (This is called "array-entering" the formula.) Thereafter,
in the formula bar you will see the formula surrounded by curly braces { }
to show that it is an array formula. Note that you cannot just type in these
braces; you must array-enter the formula.

So I was so close and yet so very very far - works perfectly, thanks Stephen!

"Stephen" wrote:

"mike_vr" wrote in message
...
Hi all,

Another relatively simple one I'm sure, but how would I get the sum of two
columns, where each cell changes and I need the product of each?

EG
A B
1 250 (250)
4 300 (1200)
3 288 (864)
7 133 (931)
2 621 (1242)
Total 4487
And then at the bottom of column B I need the sum of the products of the
two
columns.

I thought something like =SUM((A1*B1):(A5*B5)) would work but to no
avail.

So if you've got the time I'd appreciate the help.

Thanks,
Mike


The formula you need is
=SUM(A1:A5*B1:B5)
The essential point, however, is that this is an array formula. When you
type it in, don't click ENTER. Instead hold down CTRL and SHIFT whilst
clicking ENTER. (This is called "array-entering" the formula.) Thereafter,
in the formula bar you will see the formula surrounded by curly braces { }
to show that it is an array formula. Note that you cannot just type in these
braces; you must array-enter the formula.

One mo

=SUMPRODUCT(A1:A5,B1:B5)

(no need to array enter it)

mike_vr wrote:

Hi all,

Another relatively simple one I'm sure, but how would I get the sum of two
columns, where each cell changes and I need the product of each?

EG
A B
1 250 (250)
4 300 (1200)
3 288 (864)
7 133 (931)
2 621 (1242)
Total 4487
And then at the bottom of column B I need the sum of the products of the two
columns.

I thought something like =SUM((A1*B1):(A5*B5)) would work but to no avail.

So if you've got the time I'd appreciate the help.

Thanks,
Mike

--

Dave Peterson