How to Sum (Distinct Value Column 1 * the Qty in the same Row) + the Count of distinct in Column1

I am trying to create a formula that will

Sum the (Distinct Value from Column 1* the Qty in the same Row) + the Count of distinct values in Column1 for a total to be calculated in another Sheet next to the Distinct value.

There may be an easier or more straight forward way to do this but I am not sure.

Below is an example of what I am trying to achieve. Please forgive the use of Excel to quickly show what I am trying to achieve.

Find more posts tagged with

Formulas

Conditional Formatting

Cell linking

Accepted answers

Paul Newcome

You would first need to use a text/number column (called "Number" in this example) with the numbers 1 through however many distinct entries you think you will need manually entered.

etc.

Then in another text/number column (called "List" in this example) you would use the formula:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Items Column}, {Source Sheet Items Column}, @cell <> "")), Number@row), "")

And then in the next column that houses the counts you would use

=SUMIFS({Source Sheet Qty Column}, {Source Sheet Items Column}, @cell = List@row)

Paul Newcome

For those columns you can use an INDEX/MATCH.

=INDEX({Reference Sheet Column To Pull}, MATCH([Column To Match On]@row, {Reference Sheet Column TO Match In}, 0))

All comments

Paul Newcome

You would first need to use a text/number column (called "Number" in this example) with the numbers 1 through however many distinct entries you think you will need manually entered.

etc.

Then in another text/number column (called "List" in this example) you would use the formula:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Items Column}, {Source Sheet Items Column}, @cell <> "")), Number@row), "")

And then in the next column that houses the counts you would use

=SUMIFS({Source Sheet Qty Column}, {Source Sheet Items Column}, @cell = List@row)

T C

@Paul Newcome Thank you that worked perfectly!

As a follow up question...

There is a third sheet that I needed to pull information from and into sheet 2. Unlike the first issue, the "Number of Legs" and "Price" simply correspond to the animals/"Items Being Added".

How would I go about pulling that information in? (see the below updated example)
Where would I go to learn about getting better with formulas?

Thanks!

Paul Newcome

You could use another SUMIFS to pull from the Price column based on range/criteria sets for the other variables.

=SUMIFS(Sheet 3 Price}, {Sheet 3, Item}, @cell = Item@row, {Sheet 3 Number}, @cell = Number@row, {Sheet 3 Color}, @cell = Color@row)

T C

Thank you for your reply @Paul Newcome

So, this formula would be for one of the columns and I would reconfigure the formula to add any additional columns?

T C

https://community.smartsheet.com/discussion/comment/419802#Comment_419802

So, that I am no longer confusing myself...😵

Here is what I am actually putting together. I tried to see if I could recreate the logic in simplier terms and got turned around.

Below is "SieFlex Costing Summary" = "Aggregated Data on Sheet 2" from the logic I tried to create above. (I tried the logic of the formula you provided, but I am not sure where I went wrong.

Below is "Breakers and Costs" = "Sheet 3" from the logic I tried to create above.

Please advise @Paul Newcome I am so close to a great solution.

Paul Newcome

When referencing a column name that has spaces, numbers, and/or special characters, the column name must be wrapped in [square brackets].

Item Being Added@row

should be

[Item Being Added]@row

The above is true for other column references (not cross sheet references though - they use the {curly brackets}) within your formula.

T C

@Paul Newcome thanks for the tip! I still am having some other issue with the formula.

Still showing "#Unparseable"

Any additional advice will be helpful. Again, I appreciate all help. Thanks!

Andrée Starå

Hi @T C

I hope you're well and safe!

Do you have these columns in the Costing Summary sheet? It looks like they are only in the Breakers and cost sheet. If that's the case, then the formula references something that isn't in the sheet.

Screenshot of Arc at 15 Feb 2024 at 09_36_14.png

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

T C

Good day @Andrée Starå ,

I do not have those columns in the Cost Summary sheet. Great catch!

If I simply add those columns to the Cost Summary sheet will that fix the issue?

T C

Hey @Paul Newcome & @Andrée Starå

I added the columns. It is still #UNPARSEABLE. I will need a little more support 😓.

Paul Newcome

Make sure the column names in the formula are an exact match to the column names used in the sheet. For example... You have [Item Being Added]@row in your formula, but your screenshot shows the column name as [Items Being Added].

T C

That did something. Now it says #BLOCKED.

Paul Newcome

Ok. That means your formula is referencing a cell that has an error in it.

T C

I will check on everything and report back!

T C

So, I could not find the error causing the #BLOCKED. "SieFlex Costing Summary" Sheet

Here is the updated formula. "SieFlex Costing Summary" Sheet

I am still confused. The "Breakers and Costs" Sheet being referenced. Isn't that long and it is all not calculated fields below.

@Andrée Starå / @Paul Newcome thoughts?

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.