Find matching criteria, take costs, add, provide total.

Options

Howdy,

I am attempting to create a sheet that can consistently provide totals from adding up all costs on lines that have matching IDs / names. I'll explain below:

>Sheet contains multiple projects lines. We'll call these projects A and B.

>Projects A and B have multiple, not collected together, entries with costs associated on each entry line.

>Need a formula / function that can search the sheet, find all the project A entries and add up all the costs associated for a final total.

(Cannot re-order the lines every time new lines are appended to the bottom of the sheet.)

Believe this would be a pivot table in Excel, but Smartsheet only has an enterprise-level paid app for that. Is there any way to accomplish this using a formula?

Answers

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Options

    Hey @RG Sanders

    If I understand correctly it looks like this should do the trick:

    =SUM(COLLECT(Cost:Cost, [Project Name]:[Project Name], [Project Name]@row))

    This you can put in a new column - it will just show the Total Cost on each row -

    You could alternatively put this in a Sheet Summary Cell for EACH project - all you would have to do is change the "[Project Name]@row" from the end of the formula, to the specific name of the project you want to total.

    Obviously this is with your columns being named "Cost" and "Project Name" in those pertinent columns.


    Let me know if this fixes your issue or not, and if you have any additional issues!


    -Jon Mark

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @RG Sanders

    I hope you're well and safe!

    To add to John's excellent advice/answer.

    Have you explored using a Report instead with the Grouping/Summary feature?

    Would that work/help?

    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 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!