Count if + Contains?
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?
Best Answer
-
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))
Answers
-
Are you getting an error or an inaccurate count? What is the formula that outputs the "fake date"?
-
The count always returns 0. Even if I have some dates listed with that weird date of 01/01/01
-
What is the formula that outputs the "fake date"?
-
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
-
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))
-
THANK YOU!!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!