How do I build a formula that looks for duplicates that also ignores blanks or "N/A"?
I'm trying to build a formula that counts if it finds a match so it will check a box but I don't want it to count blanks or when someone enters "N/A".
=(IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row) > 1, 1))
And no matter what I try I seem to break the formula.
Answers
-
@Aubrey Jones You can add an additional If around it.
=IF(AND([Document ID #*]@row != "", [Document ID #*]@row!="N/A"),IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row) > 1, 1,0),0)
-
@sharkasits While the formula works it doesn't seem to be quite right because even if it finds a duplicate in the column it is just putting a zero for everything.
-
I figured it out. =IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row, [Document ID #*]:[Document ID #*], "<>", [Document ID #*]:[Document ID #*], "<>N/A") > 1, 1, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K 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!