COUNTIFS Date Formula and Formatting
I will try to explain this the best I can....
I am currently using data shuttle daily to populate a sheet. The imported data does not separate the date and time, so I am using formulas to separate them on my target sheet into separate columns. On a separate data sheet I am trying use a COUNTIFS formula based on a unique identifier and the month to give me the monthly occurrence count. I've tried two formulas:
=COUNTIFS({Station}, Name@row, {Date}, IFERROR(MONTH(@cell), 0) >= 2, {Date}, IFERROR(MONTH(@cell), 0) < 3)
and
=COUNTIFS({Station}, Name@row, {Date}, YEAR(@cell) = 2024, {Date}, MONTH(@cell), 0) >= 2, {Date}, MONTH(@cell), 0) < 3)
Both are returning 0, I am assuming because the date column on my target sheet is listed as Month.Day.Year. So I created a helper column that takes the data using the following formula:
=Date@row
and used the date format tool to yield the data as Year.Month.Day. However, every time the page refreshes the helper column also returns to Month.Day.Year, despite the applied formatting.
I am at a loss. Any help, guidance, or advice is most welcome and appreciate. Thank you in advance!
Answers
-
I've had trouble with using "MONTH(@cell)" in a COUNTIFS formula. The only way I've been able to get a formula like this to work is to create a helper column to extract the month from the date cell. The column formula is like so:
=IFERROR(MONTH(DATE@row), "")
I would then reference this column as a range instead in your countifs formula.
Hope this helps!:)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!