How to count a particular response type within a range based on the submission month

Options

Hi! I'm trying to count all responses to a SmartSheet form with the response "Non-Compliant" within the current month. End users will submit new responses each month and I need the formulas to automatically look for the current month of submissions. I've tried a few things, but always get incorrect argument or unpareseable. Here's what I've tried:

=COUNTIFS({Month}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {General}, "Non-Compliant") —> #INCORRECT ARGUMENT

=COUNTIFS({General}, "Non-Compliant", {Submission Month}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())) —> #INCORRECT ARGUMENT

=COUNTIF({General}, "Non-Compliant"),AND({Submission Month}=Comments1 —> #UNPARESEABLE

In the last attempt, I had a cell in the sheet where I'm producing calculations reading the current month with "=MONTH(TODAY())" - just trying different things. Any help will be greatly appreciated!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @MK Parrott,

    For the Month range, is that just a column that has the month number listed? If not, you'll want to create one that just lists the month #, then you should be able to use this formula:

    =COUNTIFS({Month}, MONTH(TODAY()), {General}, "Non-Compliant")

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • MK Parrott
    Options

    Hi @bisaacs, thank you so much! I tried that - created a column formula using =MONTH(Created@row) and am still getting the same error. Am I doing something weird with the month formula?

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @MK Parrott,

    What's the column type for the Month? Also what's the error you're getting?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • MK Parrott
    MK Parrott ✭✭
    edited 05/29/24
    Options

    Hi @bisaacs! I have a Created Date column being converted to the month number using =MONTH(Created@row) as a column formula. That formula is in a text/number column, and is working, but when I try the COUNTIFS statement using the text/number column I still get INCORRECT ARGUMENT. Let me know if I can better clarify - I'm a bit new to smartsheet!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!