Sum Collect Index Match with a calculation...

Barry D
Barry D ✭✭
edited 05/26/22 in Formulas and Functions

I have a list of items and costs associated with the item. I would like to build a formula without the use of a helper column to calculate the total Value of items based on the quantity of items.

The list of items is about 300 lines and the number of "people" will be a fluctuating quantity...

I would rather not create helper columns each time a new person is added. I realize I could do that multiplication across the rows and then total that, but if a formula could be made, it think it would be much cleaner.

Something like this is was coming to mind...

=SUM(COLLECT(INDEX(Mike:Mike, MATCH(Item@row, Item:Item, 0)) * INDEX(Cost:Cost, MATCH(Item@row, Item:Item, 0)), Mike:Mike, >0))


Best Answer

  • SJ Sellers
    SJ Sellers ✭✭✭✭
    edited 05/26/22 Answer ✓

    When I use the formula in my Sheet I get 29 and 62 respectively, which is the same as if I extend the costs in a separate column.

    In this screenshot, I used the AVGW*SUM formula in the Mike and Sam Columns, and extended the cost and summed in a "helper" column for Mike Sum and Sam Sum and get the same answer using either approach.

    I wonder if you have a typo in your formula.

    AVGW*SUM will give you the same results as SUMPRODUCT in Excel, which it sounds like what you are looking for.

    Edit: Here is the sauce:

    Sum Collect Index Match with a calculation... -


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!