How To Use Excel SUMPRODUCT Function

    how to use excel sumproduct function

    Quick Intro

    SUMPRODUCT is one of the most useful excel functions.

    Sumproduct function can help you to multiply and sum specified ranges or arrays in excel. In simple words, it will first multiply the corresponding cells from ranges and then sum up all the values.

     Definition for Excel SumProduct Function

    In above example, I have used sumproduct to multiple and sum up the values from column D and column F. First, it has multiplied the values from column D with column F and then sums up the values.

    By using excel sumproduct function you can perform a complex calculation in a single cell.

    Syntax

    SUMPRODUCT(array1, [array2], [array3], …)

    • array1 First array you want to multiply & then add.
    • [array2] Second array you want to multiply & then add.

    Additional Information on Excel Sumproduct Function

    • If skip you to specify array2, Sumproduct will simply sum the array1.
    • The maximum size of each array should be same. If array1 has 5 cells then cells in array2 should be 5.
    • Text & other non-numeric entries will be treated as 0.

    Examples

    In the below example, I have used sumproduct to get a value on some conditions.

    Examples For Excel Sumproduct Function

    V = Vendor W =Week

    As I have already mentioned, if you do not specify array2, it will simply sum array 1. And, here I have used the same logic to get the value from a condition. Let me explain.

    Examples To Understand Excel SumProduct function

    • First, it will check which vendor you have selected.
    • Then it will check which week you have selected.
    • And, return the value from the cell intersect on the both.

    The array will work something like this when you select V-3 and W-2

    How Excel SumProduct Function Works With Arrays

    Sample File

    download sample file to learn more about this tips

    What’s Next?

    Some awesome things you can do with sumproduct:

    To learn more about Excel SUMPRODUCT Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

    You also have SUMIF | SUMIFS  functions which are highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.