Sum Collect Index Match with a calculation...
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

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
Answers

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

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

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

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!