How do I structure formulas so that I receive a specific count back regarding Task Status?

AndrewShermoen

The Smartsheet above is referenced by multiple members of my organization, but I'd like to simplify some of the information for the members who only want to know specific metrics. I've obscured information that could be considered confidential.

I'd like to write formulas that report back the following information:

1. The number of documents that have been added to the sheet on the current day (for example, if 25 documents were added on 11/07 then the formula would report back 25, but if on 11/08 there were only 15 added then it would say 15).

2. The number of documents marked as "Complete" in the status column on the current day.

3. The number of documents that have been added but not marked as "Complete." I wrote a simple formula for this by just subtracting the number at the top of the "Total Complete" column from the number at the top of the "Total Requests" column.

I think I need to use the COUNTIFS function to start with on these formulas, but I'm struggling to find out how to make the formula only count cells that were changed or rows that were added on a specific date.

I'd also like to make the reports from these formulas filter into a report so that I can chart them in a dashboard.

Any help is greatly appreciated!

Thank you!

-Andrew

mcarlson

You’re on the right track with the COUNTIFS function in Smartsheet! Here’s how to structure the formulas you need, focusing on filtering by the current date. I’ll walk you through each formula, and then explain how to integrate the results into a report and dashboard.

1. Counting Documents Added on the Current Day

To count the number of documents added to the sheet on the current day, you’ll need a Date Created column (let's call it Created Date) that automatically records the date each row is added.

Assuming:

• Your Created Date column records the date a document was added.

Use this formula:

excelCopy code=COUNTIFS([Created Date]:[Created Date], TODAY())

This formula counts rows where the Created Date matches the current date.

2. Counting Documents Marked as “Complete” on the Current Day

To count documents marked “Complete” on the current day, you’ll need:

• A Status column that tracks each document's status.
• The Date Created column.

Use this formula:

excelCopy code=COUNTIFS([Created Date]:[Created Date], TODAY(), Status:Status, "Complete")

This formula checks that both the Created Date is today and the status is “Complete.”

3. Counting Documents Added but Not Marked as “Complete”

For this, you’ll also use the Status and Created Date columns. To count all documents added today that are not “Complete,” try this:

excelCopy code=COUNTIFS([Created Date]:[Created Date], TODAY(), Status:Status, <> "Complete")

This counts rows where the date is today and the status is anything other than “Complete.”

Structuring these Formulas for Reporting

1. Create a Summary Sheet: If you want to track these numbers daily, set up a separate summary sheet with columns for Date, Total Added, Completed, and Incomplete. You can use cell linking to pull these formula results into the summary sheet.
2. Automated Reporting and Dashboarding:
   • Report Setup: Once the data is on your summary sheet, set up a Smartsheet Report to pull this summary information. This report will dynamically update as your data changes.
   • Dashboard Charting: Add the report to a Smartsheet dashboard, where you can create charts (like bar or line charts) to visualize these metrics over time.

Using this structure, you’ll have a clear daily snapshot of document counts and statuses that will be easy to share with team members who need high-level insights without diving into detailed sheets.

Feel free to reach out if you need any additional help!

jessica.smith

Hi @AndrewShermoen,

If you want to know New Documents Created Today and New Documents Completed Today:

For Created

1. If not already there, add a Created Date system column to your sheet (for my formula example, assume its called "Created")
2. Add a field to the sheet summary and use formula to count new rows: =countif(Created:Created, today())
3. To add to dashboard: add as a Metric type widget

For Complete

1. Add a field to the sheet summary and use formula to rows where date in the Date Completed field is today: =countif([Date Completed]:[Date Completed],today())
2. Add to dashboard as Metric Type widget

If you want to know Number of New Documents Created on Any Given Day and Number of Documents Completed on Any Given Day:

For Created

1. Add a Created Date column to your sheet (for my formula example, assume its called "Created")
2. Add column to capture the metric: New Docs Created
3. Add column formula to New Docs Created: =COUNTIF(Created:Created, Created@row)
4. When you create your report, group by Created column and then use the Summary option to return either Min or Max from the New Docs Created field
5. Alternatively, if all you want is a widget on a dashboard and you dont actually need the column in your source sheet, you can create a report, group it by the Created Column and use the Summary option to count the number of records in each group.

For Complete

Same as above but column formula should be: =COUNTIF([Completed Date]:[Completed Date], [Completed Date]@row)

Hope I interpreted your request correctly and that this helps!

Jess Selano

mcarlson

You’re on the right track with the COUNTIFS function in Smartsheet! Here’s how to structure the formulas you need, focusing on filtering by the current date. I’ll walk you through each formula, and then explain how to integrate the results into a report and dashboard.

1. Counting Documents Added on the Current Day

To count the number of documents added to the sheet on the current day, you’ll need a Date Created column (let's call it Created Date) that automatically records the date each row is added.

Assuming:

• Your Created Date column records the date a document was added.

Use this formula:

excelCopy code=COUNTIFS([Created Date]:[Created Date], TODAY())

This formula counts rows where the Created Date matches the current date.

2. Counting Documents Marked as “Complete” on the Current Day

To count documents marked “Complete” on the current day, you’ll need:

• A Status column that tracks each document's status.
• The Date Created column.

Use this formula:

excelCopy code=COUNTIFS([Created Date]:[Created Date], TODAY(), Status:Status, "Complete")

This formula checks that both the Created Date is today and the status is “Complete.”

3. Counting Documents Added but Not Marked as “Complete”

For this, you’ll also use the Status and Created Date columns. To count all documents added today that are not “Complete,” try this:

excelCopy code=COUNTIFS([Created Date]:[Created Date], TODAY(), Status:Status, <> "Complete")

This counts rows where the date is today and the status is anything other than “Complete.”

Structuring these Formulas for Reporting

1. Create a Summary Sheet: If you want to track these numbers daily, set up a separate summary sheet with columns for Date, Total Added, Completed, and Incomplete. You can use cell linking to pull these formula results into the summary sheet.
2. Automated Reporting and Dashboarding:
   • Report Setup: Once the data is on your summary sheet, set up a Smartsheet Report to pull this summary information. This report will dynamically update as your data changes.
   • Dashboard Charting: Add the report to a Smartsheet dashboard, where you can create charts (like bar or line charts) to visualize these metrics over time.

Using this structure, you’ll have a clear daily snapshot of document counts and statuses that will be easy to share with team members who need high-level insights without diving into detailed sheets.

Feel free to reach out if you need any additional help!

jessica.smith

Hi @AndrewShermoen,

If you want to know New Documents Created Today and New Documents Completed Today:

For Created

1. If not already there, add a Created Date system column to your sheet (for my formula example, assume its called "Created")
2. Add a field to the sheet summary and use formula to count new rows: =countif(Created:Created, today())
3. To add to dashboard: add as a Metric type widget

For Complete

1. Add a field to the sheet summary and use formula to rows where date in the Date Completed field is today: =countif([Date Completed]:[Date Completed],today())
2. Add to dashboard as Metric Type widget

If you want to know Number of New Documents Created on Any Given Day and Number of Documents Completed on Any Given Day:

For Created

1. Add a Created Date column to your sheet (for my formula example, assume its called "Created")
2. Add column to capture the metric: New Docs Created
3. Add column formula to New Docs Created: =COUNTIF(Created:Created, Created@row)
4. When you create your report, group by Created column and then use the Summary option to return either Min or Max from the New Docs Created field
5. Alternatively, if all you want is a widget on a dashboard and you dont actually need the column in your source sheet, you can create a report, group it by the Created Column and use the Summary option to count the number of records in each group.

For Complete

Same as above but column formula should be: =COUNTIF([Completed Date]:[Completed Date], [Completed Date]@row)

Hope I interpreted your request correctly and that this helps!

Jess Selano

AndrewShermoen

Thank you both!

Both of these were great suggestions that helped me find the solution. I appreciate your help!