Sum divide

[benong]

hi,
i've just seen the sumproduct tutorial. Does excel has a "sum divide" function?
Can you show me the weblink to this tutorial, thanks?

[mikerickson]

Do you mean something like

=SUMPRODUCT(1/(A1:A10), 1/(B1:B10))

Anything like that has #DIV/0 problems, that can be worked around.

Do you have a specific need for a SumDivide?

[Bob Phillips]

Or maybe you mean


=SUMPRODUCT(A1:A1/B1:B5)

Perhaps you could clarify what you mean.

As to a weblink, to what tutorial?

[benong]

hi,

example:

row colA colB
1 2 10
2 1.3
3 2.5 30
-----------------
Answer: 17
-----------------

i need to find out the sum of these 3 rows.

17 = (10/2) + (1.3/0) + (30/2.5)

I have many rows of data. If i were to write the formula like above, it would takes up plenty of time!
i do not know whether excel have such a formula like sumdivide to perform the above functions.

[mikerickson]

17 does not equal (10/2) + (1.3/0) + (30/2.5).

The second term is undefined and, therefore, the sum is also.

But a variation of xld's formula might work.

perhaps


=SUMPRODUCT(B1:B3/A1:A3)

[Bob Phillips]

No, that will give an error as well. I would suggest this array formula


=SUM(IF(NOT(ISERROR(B1:B3/A1:A3)),B1:B3/A1:A3))

[benong]

it works great, thanks :)

[Paul_Hossler]

There are 3 excellent SumProduct tutorials as 'Stickies' are the top of the SumProduct sub-forum that have a very good presentation of how to use Sumproduct


http://www.vbaexpress.com/forum/forumdisplay.php?f=98

As well as

http://xldynamic.com/source/xld.SUMPRODUCT.html

Paul