How do I count planned completed action items by month?

CHackerd

Hello!

I have an End Date column and a Status column. I would like to build a formula that counts if Status is "Complete" and End Date is a designated month and year.

I would like that formula to generate a 0 if none can be counted.

End Date column type is Date, so dates appear as DAY/MONTH/YEAR. Status column type is Dropdown list.

I am new to formulas (beyond the basics) so any help would be appreciated.

Danielle Arteaga

This example assumes that you want to count projects that have an [End Date] in January 2024 (but you can make this work for any month / year you choose.)

Your sheet will need 3 "helper" columns (although you could probably condense this into a single do-it-all formula, it would be very difficult to edit / understand ...)

The [End Date] column must be formatted as a date column type. (Check your column preferences to confirm.)

In the [Year Helper] column, use this formula:

=YEAR([End Date]@row)

Set it to a column-level formula. This formula parses out the year from your End Date.

In the [Month Helper] Column, use this formula:

=MONTH([End Date]@row)

Set it to a column-level formula. This formula parses out the month from your End Date and converts it to a number. (January = 1, February = 2, etc.)

The [Count Helper] column should be a checkbox column type. In the [Count Helper] column, use this formula:

=IF(AND([Year Helper]@row = "2024", [Month Helper]@row = 1, Status@row = "Complete"), 1, 0)

This formula will place a check in the box on any row that has a status of "Complete" AND an end date in January AND in the year 2024. (You can change the year, status, or month by changing those values in the formula.)

Now, you can use your Sheet Summary to count how many projects were Complete in January 2024 using this formula in your created field:

=COUNTIF([Count Helper]:[Count Helper], 1)

You can HIDE the [Year Helper], [Month Helper] and [Count Helper] columns from your sheet if you don't want the clutter. They still work when hidden.

P.S. In case you want it, here's how you could do it without the [Year Helper] and [Month Helper] columns ... Just place this formula in the [Count Helper] column instead:

=IF(AND(YEAR([End Date]@row) = 2024, MONTH([End Date]@row) = 1, Status@row = "Complete"), 1, 0)