Smartsheet - CountIf Metrics

Options

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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!