CODEWITHSUNDEEP
excelsumexcel-formula
p0werenner
p0werenner

Reputation: 244

SUM the result of dividing two cells (SUMPRODUCT but division instead)

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.

Upvotes: 20

Views: 103134

Answers (3)

Mazen
Mazen

Reputation: 1

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

Upvotes: -2

barry houdini
barry houdini

Reputation: 46361

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

Upvotes: 9

fthiella
fthiella

Reputation: 49049

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

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

Upvotes: 29

Related Questions