Formula to Find Completed Tasks within One Month or Three Months

Bryson Isbell

First post, pretty new to Smartsheet, need some assistance with a formula.

I'm trying to Create a Sheet Summary formula to calculate the amount of Tasks completed within a month and quarter so I can represent that information as a graph in a dashboard.

Any help would be much appreciated!

Thanks!

Genevieve P.

Hi Bryson,

You can use a COUNTIFS formula to count the number of rows that say "Completed" and are in the same month as Today's month, but this would require you to have a date column type within your sheet to reference. Here is the formula I used based on my column names:

=COUNTIFS(Status:Status, "Complete", Date:Date, MONTH(@cell) = MONTH(TODAY()))

In regards to the Quarter, do you have a column in your sheet indicating what quarter each month represents?

If not, you can build a nested IF formula to look at the month in your date field and return the text you want to indicate what quarter it's in. This might look complex but it's just looking to see what this month is, and if it's 1, 2, or 3, then it's the "1st Quarter", if it's 4, 5, or 6, it's the "2nd Quarter", etc:

=IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "1st Quarter", IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 5, MONTH(Date@row) = 6), "2nd Quarter", IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "3rd Quarter", IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "4th Quarter"))))

Then you can set up another COUNTIFS formula in a different Sheet Summary field to calculate the number of completed rows in each quarter, like so:

=COUNTIFS(Status:Status, "Complete", Quarter:Quarter, "1st Quarter")

That said, if your goal is to have these statistics populate a chart widget, it would actually be better to build the two COUNTIFS formulas as cross-sheet references in their own "Chart Data" sheet, since Sheet Summary Fields can't be selected for Chart Widget data.

The only difference would be that you would replace the column references (like Status:Status) with a cross-sheet reference (by clicking on the blue "reference another sheet" button). You can read more about cross-sheet references in our Help Center (click here).

Let me know if this will work for you, or if you have any questions.

Cheers!

Genevieve

Genevieve P.

Hi Bryson,

You can use a COUNTIFS formula to count the number of rows that say "Completed" and are in the same month as Today's month, but this would require you to have a date column type within your sheet to reference. Here is the formula I used based on my column names:

=COUNTIFS(Status:Status, "Complete", Date:Date, MONTH(@cell) = MONTH(TODAY()))

In regards to the Quarter, do you have a column in your sheet indicating what quarter each month represents?

If not, you can build a nested IF formula to look at the month in your date field and return the text you want to indicate what quarter it's in. This might look complex but it's just looking to see what this month is, and if it's 1, 2, or 3, then it's the "1st Quarter", if it's 4, 5, or 6, it's the "2nd Quarter", etc:

=IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "1st Quarter", IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 5, MONTH(Date@row) = 6), "2nd Quarter", IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "3rd Quarter", IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "4th Quarter"))))

Then you can set up another COUNTIFS formula in a different Sheet Summary field to calculate the number of completed rows in each quarter, like so:

=COUNTIFS(Status:Status, "Complete", Quarter:Quarter, "1st Quarter")

That said, if your goal is to have these statistics populate a chart widget, it would actually be better to build the two COUNTIFS formulas as cross-sheet references in their own "Chart Data" sheet, since Sheet Summary Fields can't be selected for Chart Widget data.

The only difference would be that you would replace the column references (like Status:Status) with a cross-sheet reference (by clicking on the blue "reference another sheet" button). You can read more about cross-sheet references in our Help Center (click here).

Let me know if this will work for you, or if you have any questions.

Cheers!

Genevieve

Bryson Isbell

@Genevieve P This was very helpful. Thank you!

Genevieve P.

No problem!! Happy to help 🙂