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.

而不是一个总结报告,我将创建一个新的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[email protected]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} ,[email protected]). 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 Cronkthanks 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 bymarking 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.