Help with a countifs formula

Options

Hello!

I am using a formula to count if there is a checkmark in a column during a certain month and year. The formula is as follows and works perfectly:

=COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, =1, {MCFS Safety - Service Recovery Date of Incident 1}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))

My need for help if with the following:

I tried to use the same formula to count if the checkbox was unchecked and it gives me an error message. The following is what I did:

=COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, 0 , {MCFS Safety - Service Recovery Date of Incident 1}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))


Why do I get an error for the second formula when all I did was change the "1" to a "0"?

thank you!

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Jeniese Chaffin

    What error message do you receive? Is there an extra space between the zero and the next comma?

    Also, if you choose to convert later to convert the formula to a column formula, you will need to remove $'s from the formula.

    Let me know on your error message

  • Jeniese Chaffin
    Options

    Thank you! I removed the space after the "0" but it still gave me a #Invalid Data Type error message.

    This is the formula I used:

    =COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, =0, {MCFS Safety - Service Recovery Date of Incident 1}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey Jeniese

    Date functions often produce errors if there are any blanks or non dates in the range. The IFERROR function will execute a function or formula if there isn't an error, but if there is, it will insert whatever is designated - in this case a zero.

    Try this

    =COUNTIFS({MCFS Safety and Service Recovery Tracker cool}, =0, {MCFS Safety - Service Recovery Date of Incident 1}, AND(IFERROR(MONTH(@cell),0) = [Month #]@row, IFERROR(YEAR(@cell),0) = [Year #]@row))

    cheers

  • Jeniese Chaffin
    Options

    That worked PERFECTLY! THANK YOU!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!