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

excel sum excel-formula
p0werenner picture p0werenner · Nov 30, 2012 · Viewed 70.1k times · Source

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.

Answer

fthiella picture fthiella · Nov 30, 2012

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

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

Related questions

How to SUM parts of a column which have same text value in different column in the same row

I have a column with names and a column with numbers: FirstName Name Number John Smith 17 John Smith 26 Peter Smith 116 Peter Smith 25 Franck Black 17 Luke Peterson 17 Luke Peterson 37 Names with same FirstName and Name represent the same person. I need …

Read More
Adding previous cell and current cell functions

I like to have an Excel Function for: Previous cell + Current Cell = Current cell to do this function for the entire row, when I enter a new value each time, Eg. A1+A2= A2.

Read More
How use SUMIF with month & year with text criteria on external sheet

How use SUMIF with month & year with text criteria, Exp! I want to sum A B C Date Item Code QTY 01-12-16 86000 50 15-12-16 86021 20 01-02-17 86022 100 01-03-17 86023 50 Now i want sum result of only Dec-16 of 86000 on …

Read More