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
Your style is so unique compared to other folks I have
read Excel stuffs from. Thank you for posting when you’ve got the opportunity, Guess I’ll just book mark this page for future Excel books to increase my learning
Thanks Carrissa. Glad to have you learn Excel from here