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

RG Sanders

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?

Jon Mark H

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å

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!