#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Options
edited 12/09/19

Hi there - I'm new to formatting / formulas in smartsheet (and excel for that matter) and haven't had much luck creating a formula that counts tasks that are past due. While able to use conditional formatting to visually identify these items I'm trying to roll this up into a project overview; the overview/dashboard - currently includes task count by owener, by status, etc.

Am I on the right track using a COUNTIF and TODAY type functions? Perhaps a formula that would assign a count or criteria to any dates that are greater than today and, from there use a COUNTIF to count up the number of those items? My attempts thus far have returend various errors.

I appreciate any assistnce the community can offer!

-C

• edited 09/24/15
Options

You are on the right track with COUNTIF and TODAY functions. Try this...

Add a checkbox column to your sheet with a formula that will check the box if the corresponding due date is in the past.

=IF([End Date]1 < TODAY(), 1)

Then build a COUNTIF that counts the number of boxes checked.

=COUNTIF(CB:CB, 1)

CB is the name of the checkbox column. By not including row numbers, it will count the entire column and not just a range.

• Options

I am trying to do something very similar. I would like to do it without adding additional columns. Can anyone tell me why this formula will not work or what can be done to correct it.

=COUNTIF([Due Date]:[Due Date], - TODAY(),>0)

I get #MISSING OR INVALID PARAMETERS

I have also tried this with same results.

=COUNTIF([Due Date]:[Due Date], <TODAY())

• edited 03/01/16
Options

It will not work because you can only use < or > in a COUNTIF with numerical values - you cannot use dates.

From the help center article on formulas:

• Example: =COUNTIF(Done:Done, 1)
• Result: 2
• Example: =COUNTIF(Cost:Cost, ">" + 200)
NOTE: This syntax works for numeric values only.
• Result: 2
• Options

Hi Chris, can you show us a sample of the formulas, My skills are less than yours and I am trying to do some similar to what you did. I will really appreciate that.

Thanks

This discussion has been closed.