COUNTIFS with Checkboxes and between Date Range

Hi everyone!

So I need help again and hoping someone would be so kind as to help me. I need a COUNTIFS formula that will count the number of checkboxes checked, if a date column is in a certain month. I have found out to count the number of actions by month, but because of different criteria I need to count the checkboxes and have it go into sheet that shows XXX actions were complete in February 2023. Can anyone help me? Below is what we are working with so far. {CY23 eQIP Station - Background Investigati Range 1} ,1) is a checkbox column and {CY23 Intake Station - Personnel Security I Range 3} is a date column.

=COUNTIFS({CY23 eQIP Station - Background Investigati Range 1} ,1), {CY23 Intake Station - Personnel Security I Range 3}, IFERROR(MONTH(@cell), 0) = 2, {CY23 Intake Station - Personnel Security I Range 3}, IFERROR(YEAR(@cell), 0) = 2023)

Best Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 03/04/23 Answer ✓

    Hi @Peppey, you should start naming your ranges a bit more intuitively -- you're using the default range names. I always use the general naming convention "AbbreviatedSheetName_ColumnName", so I would name yours something like: "CY23_CheckBox", "CY23_Date" -- this way you won't go crazy when you have to come back and fix things. I would just simplify your date range using the DATE function:

    =COUNTIFS({CY23_Checkbox}, 1, {CY23_Date}, @cell>=DATE(2023,02,01), {CY23_Date}, @cell<=DATE(2023,02,28))

    Let me know if this helps, and please flag this post if I answered your question! Good luck!

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Hi @Peppey glad it worked!

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 03/04/23 Answer ✓

    Hi @Peppey, you should start naming your ranges a bit more intuitively -- you're using the default range names. I always use the general naming convention "AbbreviatedSheetName_ColumnName", so I would name yours something like: "CY23_CheckBox", "CY23_Date" -- this way you won't go crazy when you have to come back and fix things. I would just simplify your date range using the DATE function:

    =COUNTIFS({CY23_Checkbox}, 1, {CY23_Date}, @cell>=DATE(2023,02,01), {CY23_Date}, @cell<=DATE(2023,02,28))

    Let me know if this helps, and please flag this post if I answered your question! Good luck!

  • Peppey
    Peppey ✭✭

    Hi Lucas! This worked perfectly. I cannot believe I didn't do the same formula you provided me before. Thank you so much!

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Hi @Peppey glad it worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!