Help with Sheet Summary Formulas/Dashboard Display

Marianne Adams vfc

Hi, I'm trying to create a dashboard using only one sheet to report on task statuses by Functional Area and by Fiscal Year. I have added the fields to the Sheet Summary and selected those columns on the Task Rollup report. I don't know what formulas to use for the Functional Area (need text, not number) as well as the Fiscal Year. Is this even doable? I can create the DB by using multiple sheets but the request is to only use one.

jessica.smith

@Marianne Adams vfc

It sounds to me like you are looking for:

For each Functional Area and for each possible Fiscal Year you want to count the number of tasks with each status

Apologies if I am misinterpreting the request.

If you want to do this using the Sheet Summary feature, you'll need to add a Summary field for each combination (e.g., Functional Area_Time - Not Started) and use CountIfs functions

Example: =COUNTIFS(Status:Status, "Not Started", [Functional Area]:[Functional Area], "Time")

For functional area, that is probably a fine approach, but for Fiscal Year you are going to have to add new fields for every status every year or atleast update the existing fields with a new Fiscal Year criteria, depending on how far back you want to be able to view the data.

One alternative approach would be to add hidden helper columns for each Status type and use a formula to insert a 1 if the status matches and leave blank if it does not [e.g., =IF(Status@row = "Not Started", 1, "") ] . Then use reports to group by Fiscal Year or by Functional Area and count of sum each Status Field. Some example screen shots of what I mean are below. New Functional Areas and Fiscal Years would be automatically incorporated into the report and figure on the dashboard as they are added to the sheet without manual intervention from anyone.

Marianne Adams vfc

Hi Jessica, thank you so much for your response. You are correct in what I'm looking to do. To be honest, I'm a little lost at this point. I've added summary fields for the Functional Area for each combination. Is the first image on the sheet and the second image on the report? I'm also not sure how to add hidden helper columns. Would this be on the sheet or report?

jessica.smith

@Marianne Adams vfc

By hidden helper columns I just mean "backend" columns that are being used for metric calculations that you will lock and hide from other users. You would create them on the sheet, but hide from view. You could then add them to a row report to support your dashboard widgets.

Feel free to shoot me an email at jmsmith9990@outlook.com if you'd like some additional help getting this set up.