SUM the result of dividing two cells (SUMPRODUCT b

问题:

I'm looking to do the equivalent of SUMPRODUCT but with division. Is there a way to add the results from dividing two arrays?

Example: Column A has the "life" of an asset in years (10, 20, 10). Column B has the Value of the asset (10,000, 20,000, 20,000). I want to add the result of (10,000/10) + (20,000/20) + (20,000/10) = 4,000, but for the formula to be dynamic as I add rows with life and value.

Thanks in advance.

回答1:

Since the division is the inverse of the multiplication, you can use this:

=SUMPRODUCT(1/A1:A3, B1:B3)

回答2:

You can do that with this formula

=SUMPRODUCT(B2:B10/A2:A10)

no blanks or zeroes allowed in the column A range...

edit: missed dynamic part....either use dynamic named ranges....or you can use this array version to allow up to 1000 rows, where later ones are blank

=SUM(IF(A2:A1000<>0,B2:B1000/A2:A1000))

confirmed with CTRL+SHIFT+ENTER

回答3:

Actually the best formula is this one:

=SUMPRODUCT(SUM(E2:E10))/(SUM(D2:10))

You can also combine the formula with another one like the following:

=SUMPRODUCT(SUM(E2:10))/(count(D2:D10))

Good luck