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.
Here to learn, willing to help!
Best Answers

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.
1
2
3
4
5
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)

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

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.
1
2
3
4
5
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 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!
Here to learn, willing to help!

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)

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?
Here to learn, willing to help!

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.
Here to learn, willing to help!

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.

@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!
Here to learn, willing to help!

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.
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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?
Here to learn, willing to help!

Hey @Paul Newcome & @Andrée Starå
I added the columns. It is still #UNPARSEABLE. I will need a little more support 😓.
Here to learn, willing to help!

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].

That did something. Now it says #BLOCKED.
Here to learn, willing to help!

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

I will check on everything and report back!
Here to learn, willing to help!

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?
Here to learn, willing to help!
Help Article Resources
Categories
Check out the Formula Handbook template!