COUNTIFS and Dates

Carol-Anne Cerbone

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.

Carol-Anne Cerbone

Thank you. That worked

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!