COUNTIFS with OR
I am trying to create a function using COUNTIFS with an OR function and am struggling to find an answer to my issue. I've tried using the AI formula generator and have searched through here for answers but none have been successful.
I need a formula that will flag rows that have the same value in column 1 and column 2 OR has the same value in column 3. I am trying to flag rows with the same for first and last name OR the same email.
Right now I have this formula:
=IF(OR(COUNTIF(Email:Email, Email@row) > 1, 1, 0), IF(COUNTIFS([First Name]:[First Name], [First Name]@row, [Last Name]:[Last Name], [Last Name]@row) > 1, 1, 0))
However, this formula is flagging rows that have an email but no first name and last name.
Answers
-
I believe what you want to achieve here doesn't require a OR function. Could you check the following one?
=IF(COUNTIF(Email:Email, Email@row) > 1, 1, IF(COUNTIFS([First Name]:[First Name], [First Name]@row, [Last Name]:[Last Name], [Last Name]@row) > 1, 1, 0))
-
Unfortunately, I'd already tried this one. It causes rows with only an email and rows only with first and last name to be flagged. Not duplicates
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!