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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.