Using SUMPRODUCT Function Devoid of Cell References

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

Posted in Blog.

2 Comments

  1. in my years of using SUMPRODUCT, i’ve honestly never seen it used this way without cell reference. Kudos from Latvia

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *