COUNTIF not working against Modified date
Hi all,
A colleague of mine has come to me with an issue and I am finding myself as stumped as he is. We are trying to collect metrics based off a modified date field - one metric for before a certain date, one metric for after. One of the metrics seems to be counting something additional and we can't determine why?
When we set a filter on the main sheet, we get 1 row, which is what is expected - see screen shots.
When we try to use a formula, it is returning a result of 2 - see screen shots.
Similarly, the other metric seems to be under reporting by 1. The assumption is the error is the same for both.
If anyone has an idea of why this is happening, it would be greatly appreciated.
Thanks,
Dave
Answers
-
Hi @DKazatsky2 - I would troubleshoot by seeing what the COUNTIF formula is pulling in. You can do that with the JOIN(COLLECT) functions. That should read like this:
=JOIN(COLLECT({Modified Date},{Modified Date}, @cell>=DATE(2024,5,23)), " ")
This should pull in the actual results (with a space between them), instead of just the count.
I sometimes get a little annoyed with the native Modified Date and create a helper column that points to it and pulls the date. Such as:
=DATE(YEAR([Modified Date]@row), MONTH([Modified Date]@row), DAY([Modified Date]@row))
Then I use this helper column as my reference when I'm doing cross-sheet references. Overkill? Maybe.
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!