Sum Collect Index Match with a calculation...

Barry D

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))

SJ Sellers

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... - Smartsheet.com

SJ Sellers

=AVGW(Cost1:Cost3, Mike1:Mike3) * SUM(Mike1:Mike3)

=AVGW(Cost1:Cost3, Sam1:Sam3) * SUM(Sam1:Sam3)

Barry D

SJ, sorry that does not work, multiplying the averages is not the same.

Your Mike, solution provides and answer of $9, whereas it should be $29

SJ Sellers

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... - Smartsheet.com

Barry D

Ahh yes! I looked at the formula and It was one line off. Once corrected, it does work! .I would not have thought of the method. Thanks