Summary of different products from the same column

Benjamin Thorgersen

As a project manager for a locksmith company I use Smartsheet for many things, but would also like to use it as a summary of all products we use for each project.

Example: Sheet 1 contains 44 columns that I want a report from. Each column represents a placement for a product. Each of those 44 columns could potentially contain 50 different products.

I am looking for a way for the report to count these products based on their name, and not on the fact that there is a value in a cell. As for grouping I could only manage to place a total of 3 groups, this will not be enough in this scenario.

Matt Johnson

Hi @Benjamin Thorgersen

I would suggest a separate metric sheet or sheets depending on how you want to structure it. List all the products down the 1st column and then do cross sheet COUNTIF formulas in the 2nd column pulling from the 1st sheet.

Depending on what the end goal is, you could list all 50x44=2200 products down the 1st column and adjust the formula every 50 rows to count from the correct column. Or you could do 2 columns for each of the columns on the first sheet, one for the product and one for the count. If the same product resides in multiple columns and you want to eventually count that all up too, then maybe the first option is the easiest in the long run.

I hope that helps.

Matt

Benjamin Thorgersen

Hi @Matt Johnson.

Thank you for taking the time to answer, I really appreciate it.

I think I get an idea of what you mean. Any chance you could create an easy example of the COUNTIF?

Placing all products in the same column will not work, I need one column for each category and sub columns for their placements. Otherwise I will have 1000+ products in each dropdown list, see example under. This is only the "latch", not entirely sure how to translate this product, but it is the lock itself inside the door.

We create one new sheet for each project, which would potentially result in having to create a new "count sheet" with manual references for each project. I was hoping I would be able to do this count as a report, this way I simply just have to choose a different sheet for each summary. Or am I wrong? Ultimately I am looking for a way to do this easier in smartsheet rather then using Excel. In Excel I can duplicate the sheet and all formulas will follow to the next project.

Andrée Starå

Hi @Benjamin Thorgersen

I hope you're well and safe!

Can each product only be select in one of the columns? There's no scenario where the same product could be selected in the first and the last column, for example.

You would structure it so it's a Templatized Structure so the Metric sheet(s) would be connected with the Project, and then you can report on that project and all other projects together.

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!