IF AND COUNTIF formula not working for checkbox field

Options

Hello,

I'm working on a formula in a checkbox field that is checked when the nominee is listed more than once in the Nominee field (contact field) and the Previous Month and Current Month fields are checked.

This formula is what I've come up with but it isn't checking the Repeat Nominee field when it should:

=(IF(AND(COUNTIF(Nominee:Nominee, Nominee@row) > 1), IF(COUNTIF([Current Month]:[Current Month], [Current Month]@row), IF(COUNTIF([Previous Month]:[Previous Month], [Previous Month]@row), 1, 0))))

Any help is appreciated!

Lori

Tags:

Best Answer

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    Hello @Lori Flanigan !

    It looks like you want this new column to check whenever Previous Month, Current Month, and Repeat Nominee are checked? If so, use:

    =IF(AND([Previous Month]@row = 1, [Current Month]@row = 1, [Repeat Nominee]@row = 1), 1, 0)
    

    This is what I have below in the All Conditions Column

    This assumes that Repeat Nominee checks if Nominee is duplicated via:

    =IF(COUNTIF(Nominee:Nominee, Nominee@row) > 1, 1, 0)
    

    If this is assumption is incorrect you can nest that formula within All Conditions to bypass the need for the Repeat Nominee checkbox with:

    =IF(AND([Previous Month]@row = 1, [Current Month]@row = 1, IF(COUNTIF(Nominee:Nominee, Nominee@row) > 1, 1, 0)), 1, 0)
    

    ___________________________________________________________________________________________

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭
    Options

    Thanks, Dan,

    I'm sorry, I don't think I was as clear as I should have been in my original post.

    The formula will be in the Repeat Nominee field to check the box when all 3 conditions are met:

    1. The email address in the Nominee field is listed in the column multiple times.
    2. The Previous Month field is checked for one of the multiple times the email address is listed.
    3. The Current Month field is checked for one of the multiple times the email address is listed.

    The Previous Month field is checked only for nominations from the previous month, and the Current Month field is checked only for nominations in the current month; those fields will not be checked in the same row, as in your row 3 example.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this:

    =IF(AND(COUNTIFS(Nominee:Nominee, @cell = Nominee@row, [Previous Month]:[Previous Month], @cell = 1)> 0, COUNTIFS(Nominee:Nominee, @cell = Nominee@row, [Current Month]:[Current Month], @cell = 1)> 0), 1)

  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭
    Options

    Paul, you continue to amaze with spot-on formulas. I will deconstruct it to try and learn from it tomorrow, but for tonight, thank you very much!

    Lori

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️


    Basically we count how many times the previous box is checked for this person. Then we count how many times the current box is checked for this person. If both of those counts are greater than zero then it meets your criteria and we can check the box as a repeat.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!