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

Bill Lafferty
Bill Lafferty ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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





  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 06/10/17



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

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




  • Bill Lafferty
    Bill Lafferty ✭✭✭✭

    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.  


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


  • Yordan

    Indeed, the MS formula divides rather than to multiply.




  • 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



    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:

    Add Column E

    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!


    Hope that's helpful for anyone who reads this in the future.


    If anyone from Smartsheet is reading this, matrix operations would be great.

This discussion has been closed.