# COUNTIF when CONTAINS a date

Options

I am trying to create 2 different formulas. One for if the column has a date (any date) entered, I want it to add up only the cells with dates. The other formula will be if that same column has any cells with the word "FIRM".

=COUNTIF([IRB SUBJECT REMOVAL DATE]13:[IRB SUBJECT REMOVAL DATE]103 = DATE)

=COUNTIF([IRB SUBJECT REMOVAL DATE]13:[IRB SUBJECT REMOVAL DATE]103 = "FIRM")

I am clearly doing something wrong, as the formulas are not working. Any help would be appreciated.

• ✭✭✭✭✭✭
Options

This formula will count the number of rows in the date type column IRB SUBJECT REMOVAL DATE where the value in that column is a date.

=COUNTIFS([IRB SUBJECT REMOVAL DATE]:[IRB SUBJECT REMOVAL DATE], ISDATE(@cell))

To count just rows 13 to 103 you can use this:

=COUNTIFS([IRB SUBJECT REMOVAL DATE]13:[IRB SUBJECT REMOVAL DATE]103, ISDATE(@cell))

• Options

I have the formula for "FIRM" working now, I noticed I was missing a comma before the equals sign.

However, I still cannot figure out the date formula.

• ✭✭✭✭✭✭
Options

Hi Cheryl

It sounds like you have one column with a combination of Dates and Text. Is this a Date column type or a Text column type?

• Options

It is a date column, but is not restricted to dates only.

• ✭✭✭✭✭✭
Options

This formula will count the number of rows in the date type column IRB SUBJECT REMOVAL DATE where the value in that column is a date.

=COUNTIFS([IRB SUBJECT REMOVAL DATE]:[IRB SUBJECT REMOVAL DATE], ISDATE(@cell))

To count just rows 13 to 103 you can use this:

=COUNTIFS([IRB SUBJECT REMOVAL DATE]13:[IRB SUBJECT REMOVAL DATE]103, ISDATE(@cell))

• Options

Thank you so much!! It worked!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!