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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Are you getting an error or an inaccurate count? What is the formula that outputs the "fake date"?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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"?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
THANK YOU!!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!