Surely a simple solution exists
 

Hi all,
I need a formula to do the following (I'm sure there is a very simple
solution but it is just eluding me right now)...

Let's say for example:
I have a list of numeric values in A1:A10.
I have a list of prices in B1:B10.

In cell C1, I want the sum of (A1:A10 multiplied by B1:B10) for each
value in A1:A10 greater than zero.

To make that a little clearer, I want to iterate down column A, looking
each numeric value. If (and only if) the value is greater than zero, I
want that value multiplied by it's neighbour in column B. I then want
the sum of these products in C1.

I do *not* want to create a new column filled with the products of
column A multiplied by column B (for each value in column A greater
than zero) and then sum this column. I want to knob two birds with one
johnny do the whole lot in one formula. Is this possible (preferrably
without VBA)??

Thanks very much in advance :)


--
AJMorgan591
------------------------------------------------------------------------
AJMorgan591's Profile: http://www.excelforum.com/member.php...o&userid=27774
View this thread: http://www.excelforum.com/showthread...hreadid=473661

Hi!

Try this:

=SUMPRODUCT(A1:A10,B1:B10)

Biff

"AJMorgan591"
wrote in message
...

Hi all,
I need a formula to do the following (I'm sure there is a very simple
solution but it is just eluding me right now)...

Let's say for example:
I have a list of numeric values in A1:A10.
I have a list of prices in B1:B10.

In cell C1, I want the sum of (A1:A10 multiplied by B1:B10) for each
value in A1:A10 greater than zero.

To make that a little clearer, I want to iterate down column A, looking
each numeric value. If (and only if) the value is greater than zero, I
want that value multiplied by it's neighbour in column B. I then want
the sum of these products in C1.

I do *not* want to create a new column filled with the products of
column A multiplied by column B (for each value in column A greater
than zero) and then sum this column. I want to knob two birds with one
johnny do the whole lot in one formula. Is this possible (preferrably
without VBA)??

Thanks very much in advance :)


--
AJMorgan591
------------------------------------------------------------------------
AJMorgan591's Profile:
http://www.excelforum.com/member.php...o&userid=27774
View this thread: http://www.excelforum.com/showthread...hreadid=473661

In cell C1, I want the sum of (A1:A10 multiplied by B1:B10)
for each value in A1:A10 greater than zero.

Maybe in C1: =SUMPRODUCT(--(A1:A100),(A1:A10)*(B1:B10))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

What am I missing here?

A1 = 0
B1 = 10

A1*B1 = 0

Does not effect the final sum.

Biff

"Max" wrote in message
...
In cell C1, I want the sum of (A1:A10 multiplied by B1:B10)
for each value in A1:A10 greater than zero.

Maybe in C1: =SUMPRODUCT(--(A1:A100),(A1:A10)*(B1:B10))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Biff" wrote
What am I missing here?

My interp was simply that there could be negative values within A1:A10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--