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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Braeden

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Braeden

    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.

  • mgilkessmith
    mgilkessmith ✭✭✭✭

    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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @mgilkessmith

    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.

  • mgilkessmith
    mgilkessmith ✭✭✭✭

    @Andrée Starå this works!! Thank you!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @mgilkessmith

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!