Welcome to Excel Jet Consult blog post. In this short tutorial, we want to learn how to use the elegant SUMPRODUCT function without cell reference.
SUMPRODUCT returns the sum of the products of corresponding ranges or arrays
SUMPRODUCT Syntax =SUMPRODUCT(array1, [array2], [array3], …)
- In cell J5, we want to calculate the Total Revenue Account Manager named Connor Rulibeth, Seafood Product1, Speedy Express Ship Mode1, and Corporate Customer Type
To perform this seamless operation using SUMPRODUCT without cell references, there is need to apply Named Ranges to the Transaction table and the Summary table to the right. For this exercise, the named ranges have been applied in both tables
- In cell J5, execute the formula:
=SUMPRODUCT(((Account_Manager=Account_Manager1)*(Product=Product1))*(Ship_Mode=Ship_Mode1)*(Customer_Type=Customer_Type1)*Revenue)
- Click CTRL + Enter. The Total Revenue is ₦669.60. Task Completed
in my years of using SUMPRODUCT, i’ve honestly never seen it used this way without cell reference. Kudos from Latvia
Thanks for this