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))
Help Article Resources
Check out the Formula Handbook template!