Menu
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.
638
To conditionally sum or count cells with the OR logic, use the plus symbol (+) in between the arrays. In Excel SUMPRODUCT formulas, as well as in array formulas, the plus symbol acts like the OR operator that instructs Excel to return TRUE if ANY of the conditions in a given expression evaluates to TRUE.
Since the division is the inverse of the multiplication, you can use this:
=SUMPRODUCT(1/A1:A3, B1:B3)
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
