How to count cells containing a date within the current month?
Hello,
I am trying to build a formula that will count any cells containing a date in the current month, so I can get a total number of due dates within the current month.. This is an example of what I've been trying:
=COUNTIF(Grace:Grace, DATE(MONTH(TODAY())))
I've tried several variations of this formula but can't get it to stay current. Let me know if there's a solution!
Best Answer
-
Is "Grace" your date column? If so:
=COUNTIFS(Grace:Grace, ISDATE(@cell), Grace:Grace, MONTH(@cell) = MONTH(TODAY()))
Count if the value in the Grace column is a date and if the month in that date is the same as the current month.
Note: If you want to get year-specific, as in same month and same year, just add that parameter the same way as you did for month:
=COUNTIFS(Grace:Grace, ISDATE(@cell), Grace:Grace, MONTH(@cell) = MONTH(TODAY()), Grace:Grace, YEAR(@cell) = YEAR(TODAY()))
About the @cell part of the function:
In formulas that use SUMIF(), SUMIFS(), COUNTIF(), and COUNTIFS(), you can use the @cell parameter in the criteria of the function. The @cell parameter performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Is "Grace" your date column? If so:
=COUNTIFS(Grace:Grace, ISDATE(@cell), Grace:Grace, MONTH(@cell) = MONTH(TODAY()))
Count if the value in the Grace column is a date and if the month in that date is the same as the current month.
Note: If you want to get year-specific, as in same month and same year, just add that parameter the same way as you did for month:
=COUNTIFS(Grace:Grace, ISDATE(@cell), Grace:Grace, MONTH(@cell) = MONTH(TODAY()), Grace:Grace, YEAR(@cell) = YEAR(TODAY()))
About the @cell part of the function:
In formulas that use SUMIF(), SUMIFS(), COUNTIF(), and COUNTIFS(), you can use the @cell parameter in the criteria of the function. The @cell parameter performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
This makes a lot of sense and it worked! the @cell will help me continue to build out the sheet. Thanks so much.
-
This did not work for me. My formula is =COUNTIFS(Issuance Date:Issuance Date), ISDATE(@cell), Issuance Date:Issuance Date, MONTH(@cell) = MONTH(TODAY()))
I get #UNPARSABLE What am I doing wrong here?
Thanks for any help!
Melissa
-
I hope you're well and safe!
Try something like this.
=COUNTIFS([Issuance Date]:[Issuance Date], ISDATE(@cell), [Issuance Date]:[Issuance Date], MONTH(@cell) = MONTH(TODAY()))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå this works!! Thank you!
-
Excellent!
Happy to help!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
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!