Countifs using TODAY()

SabrinaM

@Paul Newcome

Hello Paul! I'm actually interested in using COUNTIFS for a date that is today or after today. I thought that would be simple lol, but I'm struggling and not super familiar with the @cell functions.

In short I want to count open projects based on the team lead. Is there an easier way I'm overlooking or is there a way to make the COUNTIFS formula do this for me? Example below. The column titles are "LMS Lead" and "Project End Date".

=COUNTIFS({LMS Lead},”Sabrina McDonald”), AND({Project End Date}>=TODAY())

Paul Newcome

In that case you would replace

{Cross Sheet Reference}

with

[Column name]:[Column Name]

Matthew L

Hi Sabrina,

Looks like there are two issues with this current formula. It's short one parenthesis at the end of the formula and the and function is incorrect. Try this!

=COUNTIFS(AND({LMS Lead},”Sabrina McDonald”, {Project End Date}>=TODAY()))

Here's a resource on the AND function, both of the values that need to be true for the formula to work need to both be in the and function. It's contradictory to how you would think it would work if you are writing a sentence but makes sense if you think of it as its own function.

Also just a note on the Today function, that function only updates today's date when you go into the sheet and save it or make changes. So a tip I got at Engage would be for you to set up an automated workflow to update a column with the date that you can reference instead (you can hide this row). That way the information you are looking for will always be up to date with today's current date if you want this to stay up to date in the background when you aren't using the sheet. Here's a screenshot of what that Automation could look like,

So if you were to use this way of recording the date within a [Today's Date] Column or TODAY column your formula would look like this.

=COUNTIFS(AND({LMS Lead},”Sabrina McDonald”, {Project End Date}>=[Today's Date]))

Paul Newcome

@Matthew L Your syntax is off, you don't need the AND function at all, and see below regarding quotes.

@SabrinaM

You need to remove the closing parenthesis after the first criteria, and you don't need the AND function at all. That's built in to the COUNTIFS function by default.

Also... See how your quotes are slanted but mine (below) are straight up and down? Those slanted quotes are called "Smart Quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet Formula. You will need to retype them directly in the sheet, here in the Community, or in a text editor such as Notepad (not Word).

=COUNTIFS({LMS Lead},"Sabrina McDonald", {Project End Date}, >=TODAY())

Matthew L

@Paul Newcome, Good callout on the quotes. I didn't notice that when I copied the formula Sabrina was using :)

SabrinaM

@Paul Newcome and @Matthew L

Thank you both for the insights!

I've noticed I've had trouble with the "" but felt unsure of why that was sometimes a problem. I will continue to use notepad more to clean up that kind of font issue.

I replaced my formula with the one you provided Paul and it certainly seemed to be an improvement, but it went from unparseable to #INVALID REF. I've tried adding the formula in both of the relevant columns and still have the same error. I've checked the column names to see if there are awkward spaces etc that would cause it to not function and they are correct.

Is it possible that I need that error function for columns that don't have project end dates in them? Or could a field that has more than one name break the formula so that I need to use the HAS option instead of the = function?

Paul Newcome

That error comes from a {cross sheet reference} that has not been created properly. Ensure you are following the appropriate steps to create your cross sheet reference(s).

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

SabrinaM

@Paul Newcome hmmmm well while I use those column headers on other sheets, I was not referencing another sheet- but the fields on the same sheet. I will play around with it a bit more to see if I can identify another reason the error is occurring. Thank you so much for all of your help!

Paul Newcome

In that case you would replace

{Cross Sheet Reference}

with

[Column name]:[Column Name]

SabrinaM

@Paul Newcome

Hooray!!!! 🤩

It worked, It worked, It worked!!!! I'm so sorry for that lack of diligence on my part! I, for some reason, interpreted those curly brackets as a way to refer to the entire column instead of having to specify a range since that will grow as the rows increase.

You saved me so much time and frustration! Have a wonderful weekend!

Paul Newcome

Happy to help. 👍️