nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

Counting # of items completed late

@sophie

Hi!

I have two columns. One if the Due Date and the other is the Completed Date. I would like to set-up an equation where it counts the number of times an item was completed late (after the due date). Is this possible? If yes, what is the equation?

Find more posts tagged with

Formulas

Comments

Mike Wilday

Hmmm. I swear I know a better way of doing this, but I am having trouble getting anything to work. To do this quickly, you could create a helper column and use the following IF statement =IF([Completed Date]@row > [Due Date]@row, "late")

That will write the word late in the row... then you could do a simple... =Countifs([Helper Column]:[Helper Column], "late")

Lee Joramo

You could do this with the followting equation:

=IF([Completed Date]3 > [Due Date]3, 1, "")

This will put a 1 in the column if the Completed Date was after the Due Date. Then sum the column.

Here is an example sheet https://app.smartsheet.com/b/publish?EQBCT=d3c78d81b9424434a84055dfa1e2f28d

Paul Newcome

Comparing two (or more) columns for a count at the row level without a helper column is possible with an absurd amount of nested COLLECT functions which also happens to be rather inflexible and easily broken.

I have tested and dug through the forums here and tested some more, and the best thing by far I personally have found has been to use a helper column to compare the cells at the row level and then count the helper column.

If you can find a different way, I'm all ears. Adding extra stuff in after a sheet has been in use enough to have a few thousand rows is rather tiresome.

Sign in to join the conversation!

Quick Links

Trending discussions

How to pull the title from a hierarchy at ancestor level 0
Hi All, I just want to pull the title 'BSR Tracker' in column 33 Please note there's another Blue bar in the same tracker i.e. two levels at ancestor = 0 so i just want to pull that title in. currently the formula im using but doesn't seem to be working is: =IF(COUNT(ANCESTORS([Site Name]@row)) = 0, "",…
Issue with Email Notifications via Workflow Automation
Dear Smart Community, I am experiencing an issue with a specific email ID that is not receiving notifications triggered through workflow automation. However, the user is able to receive emails when I send an update request manually. I have tested several scenarios in coordination with the IT team. For example, when…
Formula generating -1 for days since last update
Hi! I have a formula that indicates the number of days since the last update. I have 2 different projects that were updated on the same day, but one is showing 1 day since the last update and one shows -1 since last update. The only difference is that the 2nd one was submitted after 5 pm Pacific Time. It's not possible to…