Reporting Question: create a report that aggregates Column information

Paul Raphael

Hi,

I am trying to create a report that aggregates Column information based on a Condition.

For example I have the following columns:

-Track

-Track Status

-Days

I would like to know a roll-up of the status by Track and then use days as a filter criteria.

So if the days are 30, it would show status for just those that are due within 30 days. If the days are 60 it would show status of tasks due within 60 days (this would include 30). Hope that makes sense.

SS Report.JPG

I can do this pretty easily in Excel, but can't figure out how to do this in SS.

Any guidance would be greatly appreciated.

Genevieve P.

Hi Paul,

Based on this criteria it sounds like a few COUNTIFS formulas would work, as long as you had a Date column type with the due date for each task. Try something like this: 

=COUNTIFS([Date Due]:[Date Due], <TODAY(30), [Date Due]:[Date Due], >=TODAY(), [Track Status]:[Track Status], "Done")

In this example, "Date Due" is the name of my date column. "Track Status" is the name of the column that states either "Done", "On Track" etc. The formula above would count any of the rows that have a status of "Done" that are due today or within the next 30 days.

To change up what information you are looking for, change the criteria. For example, searching in the next 60 days would just be adjusting the < TODAY bit to say <TODAY(60). To change what status type you are counting, change the words in the quotes at the end.

This would allow you to have summaries within the sheet, or even in a Sheet Summary field (from the right hand menu). You could alternatively create an actual Report, specifying this criteria in the Report builder.

Let me know if I misunderstood what you were looking for! It might be helpful to see an example screen capture of how your source sheet is set up.

Cheers,

Genevieve

Paul Raphael

Hi Genevieve, thank you for your response. I will give this a shot. Essentially what I am looking to do is to show rolled up status by track by a date range. So the result would be the mocked up report I've attached. This would should all that are due within 60 days, so if a line item was due in 90 days, it would not show up on the report. I also attached a screen capture. Based on this, will these formulas work?

Thank you so much!

Status Report.JPG

Capture.JPG

Genevieve P.

Hi Paul,

Using the Sheet Summary field for these formulas looks like it would work well for what you're looking to do! The COUNTIFS formula is definitely the type of formula to use, but based on your screen capture you'd likely have to add in another criteria to only count specific Tracks (Corp Dev, etc).

Here's a screen capture example:

This is my example formula looking for all of Corp Dev's In Progress tasks within the next 30 days:

=COUNTIFS([Completion Date]:[Completion Date], <TODAY(30), [Completion Date]:[Completion Date], >=TODAY(), Tracks:Tracks, "Corp Dev", Status:Status, "In Progress")

You'll need a slightly different formula for each of the summaries that you're looking to do. You can add additional criteria to a COUNTIFS formula by adding in the range, then a comma, then the criteria for that range. You may also want to check out our Help Center articles:

• Sheet Summaries: https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary
• COUNTIFS: https://help.smartsheet.com/function/countifs
• TODAY function: https://help.smartsheet.com/function/today
• Formula operators list: https://help.smartsheet.com/articles/2476171

If you get stuck on any of them, post what you've got so far and I'm happy to take a look and help out.

Cheers!

Genevieve

Paul Raphael

Thank you, I will give this a shot! Appreciate your help!

Paul Raphael

Hi Genevieve,

Thank you, this worked for me. So just to clarify, I need to create a summary field for every combination for each track. So if I want to report on each track, I need to replicate this for each status and time period. So if I have 5 project statuses and 30, 60, 90 day time periods that would be 15 fields I need to create. Is that correct?

Genevieve P.

Hi Paul,

Yes, that's correct.

If it seems like a lot of data to keep in the Summary Field, you could build the same formulas in a separate sheet, instead. This would visually look closer to your screen capture, but then the data would be stored in a different sheet.

You would need to adjust the formulas to use cross-sheet references instead of column references but the structure would be the same.