Report on a Grouping

Umesh Shah

I have a source sheet which has STATUS, ACTIVITY and LEAD names.

I am trying to get a summary report or report or anything to be honest where i can report on the number of activities by Lead and Status.

Here is the sample data:

What i would like to be able to produce is a table which shows each lead the number of activities by STATUS

Jack #Published Activities #Cancelled Activities

Brenda #Published Activities #Cancelled Activities

etc...

Mark Cronk

Hi @Umesh Shah,

You can do this as summary fields and then put the summary fields into a report. You'll need 3 fields for each person: planned, published, cancelled. The formula for Jack's planned activities would be: =COUNTIFS([activity status]:[activity status], ="planned", [lead:lead],="Jack") repeat for each combination.

Rather than a summary report, I would create a new grid and bud your table in it. The first cumn would be the Lead, 2nd column Planned, 3rd published and 4th cancelled. Enter lead names in column 1. This format will let you reference lead@row in your count formula rather than entering each name. You'll use a cross sheet reference to return your counts. The formula in column 2 would be =COUNTIFS({insert reference toActivity Status}, ="planned", {insert reference to your main grid lead column} ,=lead@row). For column 3 change "planned" to "published" and 4 to "cancelled". Once row 1 is done you can copy columns 2-4 down to complete the table.

Hope I made this clear enough to understand. Let me know if it works for you.

Mark

Umesh Shah

@Mark Cronk thanks for the suggestion. This only works if i have a defined list of people to list in my table. If the people list is dynamic and gets added to in the source, there is no easy way to create a unique list in one sheet based on a table in another sheet. This then breaks your suggestion above.

I wish Smartsheet had a formula to get unique values from another sheet.

Andrée Starå

Hi @Umesh Shah

To add to Mark's excellent advice/answer.

One idea would be to copy any new value to a so-called helper sheet.

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Umesh Shah

@Andrée Starå the entries are submitted via form into the sheet, the hope it to have it automatically update the grid and associated calculations. Manually updating it would not be ideal and would be prone to being out of date very quickly.

The beauty of Smartsheet is to be able to setup a sheet, formulas and dashboard which get updated automatically.

Any way this can be done?

Andrée Starå

@Umesh Shah

It would be automatic with a copy row workflow.

Would that work?

Umesh Shah

@Andrée Starå if you refer back to my original post in this thread, i am not trying to copy the row, i am trying to determine a unique list of "lead" names for which I can run some calculations on. As new rows come into my source sheet, i want the unique set of "lead" names (in another sheet) to be automatically updated should a new lead be submitted.

Whether someone submits a new row for an existing lead or a new row for a new lead, that other sheet should update the calculations per lead as needed.

If a new name pops in, it should add the name as a unique person then calculate the required fields.

So a new row being copied would not work, I only need unique names to be reflected in the calculation sheet.

As far as I know, there is no way to do this which is disappointing.