Documentation/Calc Functions/SUMPRODUCT

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.
    Other languages:


    Function name:

    SUMPRODUCT

    Category:

    Array

    Summary:

    Multiplies corresponding elements in the given arrays, and returns the sum of those products. You can use SUMPRODUCT to calculate the dot or scalar product of two vectors.

    A call to the SUMPRODUCT function need not be entered as an array formula.

    Syntax:

    SUMPRODUCT(Array 1[; Array 2;][...;[Array 255]])

    Returns:

    Returns a real number that is the sum of the calculated products.

    Arguments:

    Array 1 contains the first array to be used in the multiplication and summation. This argument can take the form of an explicit cell range (such as A1:C3), the name of a named range, the name of a database range, or an inline constant array.

    Array 2Array 255 contains the other arrays to be used in the multiplication and summation. These arguments can each take one of the forms listed for Array 1. The number of entries in Array 1 must be the same as the number of entries in each of Array 2Array 255.

    • If the number of entries in Array 1 is not equal to the number of entries in any of the other array arguments Array 2Array 255, then SUMPRODUCT reports a #VALUE! error.
    • If any cell within Array 1 ... Array 255 is empty or contains non-numeric data, then that cell is ignored by SUMPRODUCT.

    Additional details:

    Details specific to SUMPRODUCT function

    • SUMPRODUCT calculates and returns the following:
    [math]\displaystyle{ \sum_{i}^{N} Array1_{i} \times Array2_{i} \times ... \times Array254_{i} \times Array255_{i} }[/math]
    where N is the number of entries in each array argument.
    • SUMPRODUCT can also be used to sum cells where a specified condition is true – see examples below.


    General information about Calc's array functions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing Calc's array functions.

    To avoid repetition within the subsequent text, only Windows/Linux key combinations are specified. The following table shows the mapping between these key combinations and their macOS equivalents.

    Mapping between relevant Windows/Linux and macOS key combinations
    Windows/Linux key combination macOS equivalent Comments
    Ctrl + ⇧ Shift + ↵ Enter ⌘ Cmd + ⇧ Shift + ↵ Enter Create an array formula.
    Ctrl + / ⌘ Cmd + / Select all cells in an array formula range. "/" is the division key on the numeric keypad.
    Ctrl + C ⌘ Cmd + C Copy.
    Ctrl + V ⌘ Cmd + V Paste.
    • An array is a rectangular range of cells containing data. For example, a square range of three rows by three columns is a 3 x 3 array. The smallest possible array is a 1 x 2 or 2 x 1 array, with two adjacent cells.
    • A formula in which the individual values in a cell range are evaluated is referred to as an array formula. The difference between an array formula and other formulas is that the array formula deals with several values simultaneously instead of just one.
    • If you create an array formula using the Function Wizard, you must mark the Array check box so that the results are returned in an array. Otherwise, only the value in the upper-left cell of the array being calculated is returned.
    • If you enter the array formula directly into the cell, you must use the key combination Ctrl + ⇧ Shift + ↵ Enter instead of just the ↵ Enter key. Only then does the formula become an array formula.
    • An array formula is displayed inside curly brackets or braces ("{" and "}"). It is not possible to create an array formula by manually entering these braces.
    • Some array functions take parameters that are forced to evaluate as an array formula, even when the formula is entered normally. These are MDETERM, MINVERSE, MMULT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, and SUMXMY2.
    • Calc supports constant inline arrays in formulas. An inline array is enclosed within curly brackets or braces ("{" and "}"). Individual elements can be numbers (including negatives), logical constants (TRUE, FALSE), or literal strings. Non-constant expressions are not allowed. Arrays can be entered with one or more rows, and one or more columns. All rows must comprise the same number of elements; similarly, all columns must comprise the same number of elements.
    • Editing array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line, edit the formula as required, and use the key combination Ctrl + ⇧ Shift + ↵ Enter.
    • Deleting array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Press Delete to delete the array contents, including the array formula. Alternatively, press ← Backspace to bring up the Delete Contents dialog box, select Formulas or Delete all, and click OK.
    • Copying array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line. Copy the formula by pressing Ctrl + C. Select a range of cells where you want to insert the array formula and either press F2 or position the cursor in the Input line. Paste the formula by pressing Ctrl + V in the selected space and confirm it using the key combination Ctrl + ⇧ Shift + ↵ Enter. The selected range now contains the array formula.
    • Adjusting an array range. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Below the selection, to the right, you will see a small icon with which you can zoom in or out on the range using your mouse. When you adjust the array range, the array formula will not be adjusted automatically - you are only changing the range in which the results will appear.

    Examples:

    Formula Description Returns
    =SUMPRODUCT({1,2,3,4,5,6}; {3,4,5,6,7,8}) Here the function sums the products of the six numbers in each array. The value to be returned is calculated as (1*3) + (2*4) + (3*5) + (4*6) + (5*7) + (6*8). 133
    =SUMPRODUCT(A1:B2; F1:G2) where cells A1, A2, B1, B2, F1, F2, G1, and G2 contain the values 4.7, -8.5, 2.7, 8.9, 4.6, -4.7, 2.1, and 0.1 respectively. Here the function sums the products of the four numbers in each array. The value to be returned is calculated as A1*F1 + B1*G1 + A2*F2 + B2*G2. 68.13
    =SUMPRODUCT(array_data) array_data is a named range covering a 2 x 6 cell area containing the values {1, 2, 3, 4, 5, 6; 7, 8, 9, 10, 11, 12}. Here there is only one array argument and so the function simply sums the twelve numbers in the array. 78

    The following data can be used to provide simple examples of the use of SUMPRODUCT for conditional counting.

    A B C D
    1 Region Category Employee Sales Value
    2 North Tennis Kurt $410
    3 East Golf Hans $1,508
    4 South Sailing Ute $2,340
    5 East Tennis Brigitte $4,872
    6 East Tennis Fritz $2,623
    7 South Tennis Fritz $3,821
    8 South Golf Fritz $3,739
    9 West Golf Ute $4,195
    10 East Golf Hans $2,023
    11 West Tennis Hans $2,804
    12 West Sailing Ute $579
    13 North Tennis Kurt $1,858
    14 East Tennis Hans $2,827
    15 North Golf Ute $4,482
    16 South Golf Hans $1,539
    Formula Description Returns
    =SUMPRODUCT(A2:A16="North"; D2:D16) Here the function sums the sales values (cells D2:D16) for those rows representing the North region (cells A2:A16). $6,750
    =SUMPRODUCT(B2:B16="Golf"; C2:C16="Hans"; D2:D16) Here the function sums the sales values (cells D2:D16) for those rows representing a specific employee Hans (cells C2:C16) and the Golf category (cells B2:B16). $5,070
    =SUMPRODUCT((A2:A16="North") + (A2:A16="East"); D2:D16) Here the function sums the sales values (cells D2:D16) for those rows representing the North region or East region (cells A2:A16). $20,603

    Related LibreOffice functions:

    None.

    ODF standard:

    Section 6.16.64, part 2

    Related (or similar) Excel functions:

    SUMPRODUCT