COUNTIFS and Dates

Hi everyone -

I am working on a new process for one of my business units. They have multiple date fields within the sheet. I need to count the number of dates that are either today or in the past. If a date is entered in the future then they don't want to count it until the date is reach. For example:

Date 1 = 09/01/2021

Date 2 = 2/23/2022

Date 3 = 8/01/2022

In counting, I should only have a value of 2 until the other dates are reach but my formula is only returning a value of 1.

Here is my formula: =COUNTIFS([Date 1]@row, <=TODAY(), [Date 2]@row, <=TODAY(), [Date 3], <= TODAY())

Also, if the date field is blank, how do I not show a value of 0?

Hopefully that makes sense.

Find more posts tagged with

Accepted answers

Carol-Anne Cerbone

Thank you. That worked

All comments

Jeff Reisman

You are asking COUNTIFS to evaluate if ALL the conditions you give are true, and if so, to return a value of true (aka "1".) It can't evaluate [Date 3] at all because you left off the @row after the column name. So instead of returning 0, it returned 1 since the the two criteria it could evaluate returned true. Split these apart into separate COUNTIF statements and add the results together:

=COUNTIF([Date 1]@row, <=TODAY()) + COUNTIF([Date 2]@row, <=TODAY()) + COUNTIF([Date 3]@row, <=TODAY())

Carol-Anne Cerbone

Thank you. That worked

Jeff Reisman

@Carol-Anne Cerbone

Glad it helped. If you could mark my answer as accepted I would appreciate it!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.