IF AND COUNTIF formula not working for checkbox field

Lori Flanigan

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

Paul Newcome

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)

SoS | Dan Palenchar

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!

Lori Flanigan

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

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

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

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.