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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!