Smartsheet - CountIf Metrics

MACChelsea

Hello,

I am working on building a dashboard and starting with my metrics sheet. I cannot figure out the formula that I need to populate the correct information.

I am needing it to pull from "Sheet A" the number of rows whose status is "Complete" and the "Date Submitted" was in Q1 2024. I will be doing this for multiple status/ quarters but as long as I have the first correct formula, I can figure out the rest.

Thanks

Kelly Moore

Hey @MACChelsea

There's a few ways to do this, depending on how your metric sheet is set up. One option is to add a helper column to your sheet A and designate the quarters on that sheet. This would allows you the option of pulling the data in via report or pivot App - which automates the data pulling from that point forward.

If interested in adding a helper column, try this formula:

=IF(MONTH([Date Submitted]@row⇐3,"Q1",IF(MONTH([Date Submitted]@row⇐6,"Q2",IF(MONTH([Date Submitted]@row⇐9,"Q3","Q4")))+" "+YEAR([Date Submitted]@row

If you build a report and group on this helper column and summarize to count on your status, you should have all of your data. You can use this report to build a chart in your dashboard. It should be 'hands free' after that.

Would this work for you?
Kelly

If needed, please see this help article on reports.

https://help.smartsheet.com/articles/2482078-build-a-row-report-with-report-builder#toc-create-a-row-report