# Count if + Contains?

Options
✭✭✭

I have a column of dates. My data sometimes will give a "fake date" of 01/01/01. I'm trying to have that specific date counted. My formula is:

=COUNTIFS({FLEETCARE UPLOAD Range 2}, CONTAINS("01/01/01", @cell))

But it doesn't seem to be working?

Any ideas?

• ✭✭✭✭✭✭
Options

Ok. If you are outputting a date then you need to search for a date.

=COUNTIFS({FLEETCARE UPLOAD Range 2}, @cell = DATE(2001, 01, 01))

• ✭✭✭✭✭✭
Options

Are you getting an error or an inaccurate count? What is the formula that outputs the "fake date"?

• ✭✭✭
Options

The count always returns 0. Even if I have some dates listed with that weird date of 01/01/01

• ✭✭✭✭✭✭
Options

What is the formula that outputs the "fake date"?

• ✭✭✭
Options

The date is imported as: 1/1/0001 12:00:00 AM(MST)

Then I'm using the following formula to just grab the date and exclude the time stamp:

DATE(VALUE(MID(Last@row, FIND("/", Last@row, 4) + 1, 4)), VALUE(LEFT(Last@row, FIND("/", Last@row) - 1)), VALUE(MID(Last@row, FIND("/", Last@row) + 1, FIND("/", Last@row, 4) - (FIND("/", Last@row) + 1))))

This leave me with 01/01/01 , and I just want to count how many have that date. I tried: =COUNTIFS({FLEETCARE UPLOAD Range 2}, CONTAINS("01/01/01", @cell)) but it always produces 0

• ✭✭✭✭✭✭
Options

Ok. If you are outputting a date then you need to search for a date.

=COUNTIFS({FLEETCARE UPLOAD Range 2}, @cell = DATE(2001, 01, 01))

• ✭✭✭
Options

THANK YOU!!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!