Counting Status of Requests for CURRENT Month

Jodi Caplan

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

Heather Duff

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

Heather Duff

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

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

Perfect - Glad it worked!!