how to write formulas to get the answer of "?" in pic below.
The date are in string (manually typed)
I want to count how many different date in a month
for example 22-jan-20 and 21-jan-20 are counted as 2
Please help me for the smartsheet formula
Best Answer
-
Thank you for clarifying! Yes, in that case we would need to add in a DISTINCT function as well, to only count the unique times that this string appears while containing "Jan":
=COUNTIF(DISTINCT(Dates:Dates), CONTAINS("Jan", @cell))
Does this now meet all of your criteria?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
You can use a combination of a COUNTIF formula and a MONTH function (using IFERROR to get rid of any blank cells).
Try this for your January count:
=COUNTIF(Dates:Dates, IFERROR(MONTH(@cell), 0) = 1)
Then to change what month you're looking for, just change the 1 at the very end of the formula to be that month's number. For example, if I was looking for April, I'd use this:
=COUNTIF(Dates:Dates, IFERROR(MONTH(@cell), 0) = 4)
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
My apologies Michael, I just read that your dates are in a string, not in a Date Type of column. The formula above is for a Date Type of column.
In this instance, you would need to use CONTAINS to check and see if your dates column contains a certain word. Please note that I've titled my column "Dates" for the range, so you will need to swap this out for your current date column name:
=COUNTIF(Dates:Dates, CONTAINS("Jan", @cell))
Then for each Month, you would want to change the value "in these" that you are searching for:
=COUNTIF(Dates:Dates, CONTAINS("Apr", @cell))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you for responding
The formula doesn't work. But even if it is working, I don't think that I will get the answer that I want.
If you look at the example pic, there are 3 "January" but only 2 with different date.
I don't want to count the same date.
-
This one works, but it will count month with the same date.
The problem is I don't want to count multiple date
-
Thank you for clarifying! Yes, in that case we would need to add in a DISTINCT function as well, to only count the unique times that this string appears while containing "Jan":
=COUNTIF(DISTINCT(Dates:Dates), CONTAINS("Jan", @cell))
Does this now meet all of your criteria?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Marvelous, this works well. I know that it will work with distinct, but apparently I'm not smart enough to write it in this order.
Thank you very much!!! Really appreciate your help.
🍪 here's a cookie for you.
-
Haha no problem at all! Thank you for the cookie 😋
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!