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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!