Counting Status of Requests for CURRENT Month

Options

Hi,

I want to create a formula on the request metrics form that counts the number of requests "not started", "in progress", and "completed" within the CURRENT month. My column for the status' is named "Status". Please assist.

Thanks!

Jodi

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Jodi,


    I'd suggest that you add a column (which can later be hidden) called Month. In that column, you'll enter the following formula: (This assumes your date column is called Date.)

    =MONTH(Date@row)

    Then you can insert the following formula in your summary section - being sure that you are not entering the data in the Date or Status columns:

    =COUNTIFS(Month:Month, MONTH(TODAY()), Status:Status, "not started")

    You would then change "not started" to the other statuses you want to count.

    This translates to: Count every row where the month number listed in the Month column matches today's month, AND the Status is "not started."


    Note - if this sheet spans more than a year, then you will want to add a Year column (which can be hidden), using this formula:

    =YEAR(Date@row)

    Then your summary formula would be this, in order to restrict the count to only rows that are this month AND this year:

    =COUNTIFS(Month:Month, MONTH(TODAY()), Year:Year, YEAR(TODAY()), Status:Status, "not started")


    Hope this helps!


    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Jodi,


    I'd suggest that you add a column (which can later be hidden) called Month. In that column, you'll enter the following formula: (This assumes your date column is called Date.)

    =MONTH(Date@row)

    Then you can insert the following formula in your summary section - being sure that you are not entering the data in the Date or Status columns:

    =COUNTIFS(Month:Month, MONTH(TODAY()), Status:Status, "not started")

    You would then change "not started" to the other statuses you want to count.

    This translates to: Count every row where the month number listed in the Month column matches today's month, AND the Status is "not started."


    Note - if this sheet spans more than a year, then you will want to add a Year column (which can be hidden), using this formula:

    =YEAR(Date@row)

    Then your summary formula would be this, in order to restrict the count to only rows that are this month AND this year:

    =COUNTIFS(Month:Month, MONTH(TODAY()), Year:Year, YEAR(TODAY()), Status:Status, "not started")


    Hope this helps!


    Best,

    Heather

  • Jodi Caplan
    Options

    Thanks @Heather D , this worked, I realized after the fact and was wondering - how could I could add a year aspect as well since I have two years worth of data (so it is this year + this month). Thanks

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Perfect - Glad it worked!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!