Any advice on creating this report?

Hannahsamd

I am trying to recreate this excel template in a smart sheet report. I am needed some help with how to create some subtotals and split up by date. It is eventually going to be displayed in a dashboard

any advice would be helpful!!

AravindGP

Create a helper column in your source sheet to identify the month. You can use the formula =MONTH([Program Effective Date]@row). This will render a result of a number between 1 to 12. 1 being January, 2 being February, and so on. You can then use a COUNTIFS formula referencing the source sheet, like =COUNTIFS({Month}, 1, {Tier}, [Primary Column]@row)

I have included Month in curly brackets here as an example for cross sheet reference where you pull data from the newly created helper column. Note that you will need to change the criteria from 1 to 2 for February, and to 3 for March, etc. Tier in curly brackets refers to the cross sheet references where you pull data from the TierNAM Region column to match with the values such as T1:Custom1, T1:Custom2, etc.

AravindGP

Hi @Hannahsamd

You will probably need to create a metrics sheet with the same columns you need (i.e., Jan, Feb, Mar, Apr, May, etc.) to get the count of items and have a column in there to segregate it as just T1 or T2 (without the custom values. Your rows will be the same as T1: Custom1, T1: Custom2, etc.

When you pull your report from this new sheet, you can group it by the T1 or T2 column and summarize the values on top.

Note that the total on the right will need to be a column you create in the metric sheet to get the sum across all months through a formula

Tim Starkey

Hi @Hannahsamd - So I understand, are you trying to create a report that looks similar to your excel, using information stored in the sheet you are showing? You will be pretty limited in how you can format a report, and it will not look similar to your example. You can group the report by what I assume are your Tier Regions (T1, T2), and in the report use summaries.

The group sections of your report will this automatically subtotal. It puts the subtotals at the top, so you can collapse the group. Below is what a grouped report looks like on a dashboard that has two summary columns. First summary is showing total number of projects with past due tasks by manager, second summary is is total number of tasks past due.

I would need to understand a little more of your goal to help.

Hannahsamd

Yes I am trying to create that excel sheet in smart sheet. There is a much larger Smartsheet that it is connected too with a lot of the same information.

Hannahsamd

@Tim Starkey How do you recommend putting the subtotals of each month into the report?

Hannahsamd

@AravindGP can you show an example of how you would make a metric sheet with months? I understand but am having a hard time visualizing this

AravindGP

@Hannahsamd I have given screenshots of a sample metric sheet and the report. I have manually added the values in the metric sheet in the example. You will need to use COUNTIFS to get the values against each category.

Hannahsamd

@AravindGP What COUNTIFS equation do you suggest using for this? I am struggling to just get the month of January and etc into the equation

Hannahsamd

The criteria is to count every Shine Contract ID in the month in 2023 that is in each of those regions. I am needing help with the date and month part

AravindGP

Create a helper column in your source sheet to identify the month. You can use the formula =MONTH([Program Effective Date]@row). This will render a result of a number between 1 to 12. 1 being January, 2 being February, and so on. You can then use a COUNTIFS formula referencing the source sheet, like =COUNTIFS({Month}, 1, {Tier}, [Primary Column]@row)

I have included Month in curly brackets here as an example for cross sheet reference where you pull data from the newly created helper column. Note that you will need to change the criteria from 1 to 2 for February, and to 3 for March, etc. Tier in curly brackets refers to the cross sheet references where you pull data from the TierNAM Region column to match with the values such as T1:Custom1, T1:Custom2, etc.

Hannahsamd

@AravindGP Thank you so so much!! That helped so much. How did you get to Totals column to work?

AravindGP

@Hannahsamd The Totals column in the calculation sheet is a simple =SUM(Jan@row:Dec@row). The subtotals you find in the report is through Summary function (shown in the response from Tim Starkey in this thread)