3.5.2.45 Sumproduct

Sumproduct-func

Description

This function multiplies the corresponding elements in the given datasets and returns the grand total. Non-numeric values are treated as 0.

By default the datasets/ranges are multiplied together and are listed comma-separated. You may replace the commas with another operator symbol (+, –, /, *) to add, subtract, divide or multiply. if you do, enclose the datasets in parentheses to clarify the order of operations.

Syntax

double sumproduct(dataset vd1[, dataset vd2,...])

Parameters

vd1, vd2, ...

A dataset or a (range of) column. Eeach dataset should share the same number of items. Otherwise will return missing (--).

The datasets are separated by comma "," by default, which means they will be multiplied together. You can also separate the datasets by other operation symbols, i.e. "+" to sum, "-" to subtract , or "/" to divide. In this case, dataset/range notation should be enclosed with parentheses to clarify the order of the operations.

Return

Returns the sum of all multiplied elements.

Example

Example 1:

The following codes show you how “sumproduct” function works with mixed operations.

newbook;
wks.ncols = 4;
col(A) = {1, 1, 2, 2, 3, 3};
col(B) = {1, 2, 3, 4, 5, 6};
col(C) = {3, 4, 5, 7, 9, 11};
col(D)[1] = sumproduct(col(A), col(B),col(C)); //should return 430 
// In following scripts, SUMPRODUCT first evaluates the conditional test (TRUE=1, FALSE=0), 
// uses 1/0 flags to perform desired calculation with B&C, and then sums the results.
col(D)[2] = sumproduct((col(A)<=2), (col(B)+col(C))); //should return 29 
col(D)[3] = sumproduct((col(A)<=2) + col(B)+col(C)); ////should return 64
col(D)[4] = sumproduct((col(A)<=2)*col(B)+col(C)); //should rerun 49

Example 2:

In the below table, we want to sum up the Product A from Sales department.

Enter =sumproduct((A1:A7==A10$),(B1:B7==B10$),C1:C7) in cell C10.

Example 3:

The following codes first import a sample data of a medication intervention study, and then uses sumproduct to calculate the mean recovery time for different drugs.

fname$ = System.path.program$ + "Samples\Graphing\Categorical Data.dat";
newbook;
impASC;
wks.nCols = 5;
col(E) = Unique(col(D))$;
col(F)[1]$ = "=sumproduct((D==E1$),B)/sumproduct((D1:D0==E1$))";
col(F)[2]$ = "=sumproduct((D==E2$),B)/sumproduct((D1:D0==E2$))";
col(F)[3]$ = "=sumproduct((D==E3$),B)/sumproduct((D1:D0==E3$))";