#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Weighted Average & SUMPRODUCT

Options
✭✭✭✭
edited 12/09/19

Is SUMPRODUCT supported by SmartSheet?  If so, what would the syntax look like?

Thanks.

Bill

Tags:

• ✭✭✭✭✭✭
edited 06/10/17
Options

William,

UPDATE:

My previous answer (removed) was not correct.The SumProduct is

=AVGW(array of numbers, array of weights) * SUM (array of weights)

Craig

• ✭✭✭✭
Options

I did find a workaround that worked for me.

To find a weighted average, follow these steps:

1. In a new worksheet, type the following data:

A1: Cost B1: Cases A2: \$.20 B2: 10 A3: \$.30 B3: 40

2. The SUMPRODUCT formula below that you would normally use in excel would not work as it is not supported in SmartSheet.

=SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)

3. So, I used this formula which accomplishes the same thing (just had to hide a few colums):

=((A2*B2)+(A3*B3))/SUM(B2:B3)

• Options

Indeed, the MS formula divides rather than to multiply.

=SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)

or

=((A2*B2)+(A3*B3))/SUM(B2:B3)

• Options

I just wanted to comment here because this is the top search result for "SUMPRODUCT Smartsheet" in google. This is not related to weighted averages (which in the past I've accomplished using multiple countifs/sumifs and multiplication/division depending on what I'm trying to accomplish).

SUMPRODUCT in excel multiplies the values in arrays and then sums those products. The arguments for SUMPRODUCT can be equations, functions, and comparisons which are translated into boolean values.

An example:

Column A values: True, False, True, False, True

Column B values: Big, Small, Small, Big, Big

Column C values: 10, 9, 8, 9, 10

Column D values: 5, 6, 7, 6, 5

SUMPRODUCT(--(A1:A5="True"),--(B1:B5="Big"),(C1:C5-D1:D5))

The excel sheet translates this into the following arrays:

{1, 0, 1, 0, 1}, {1, 0, 0, 1, 1}, {5, 3, 1, 3, 5}

Multiplies the terms together like so:

{1 * 1 * 5, 0 * 0 * 3, 1 * 0 * 1, 0 * 1 * 3, 1 * 1 * 5}

Sums the array:

{5 + 0 + 0 + 0 + 5} = {10}

(Note: Excel can be finicky based on formatting of the cells)

To my knowledge there isn't a way to do this in Smartsheet directly, because you can't evaluate the delta between column C & D in the same formula as the sum.

What you CAN do is add a column for the delta between those values, and then use a SUMIFS statement to sum those values only if A = True and B = Big.

Continuing from the above example:

Column E values: "=[Column C]1-[Column D]1" etc.

Formula: "=SUMIFS([Column E]:[Column E], [Column A]:[Column A], "TRUE", [Column B]:[Column B], "Big")"

Adding calculation columns is often the solution in Smartsheet. I suggest locking and hiding those if other people may be using your sheets!