Countifs and Isblank is not working

I am using a formula in a column, "Repeat Values" that checks another column, "Document Number", for repeat values and if there are repeat values it checks the checkbox in the "Repeat Values" column. This formula is somewhat working, but also checks the box for all blank cells in the "Document Number" column.
=IF(COUNTIFS([Document Number]:[Document Number], [Document Number]@row) > 1, 1)
I also have a conditional format that checks for checked boxes and if the box is checked, it highlights the repeats in the "Document Number" column. It is also highlighting the blank cells.
I'm not sure if it is doing this because there is also a formula in the "Document Number" column or what.
Any advise on how to modify the formula so it doesn't check the box if the "Document Number" column is a blank field?
Best Answer
-
Hello @Genevieve P. ,
It did not work. However I kept plugging away trying to figure it out between yesterday and today and this finally worked.
=IF(ISBLANK([Document Number]@row), 0, IF(COUNTIFS([Document Number]:[Document Number], [Document Number]@row) > 1, 1))
Thank you!
Answers
-
Hi @Joni S
We can add a second IF statement at the beginning of your formula to say that If the Document Number cell is blank, return blank, otherwise...calculate the rest of your formula.
Try this:
=IF([Document Number]@row = "", "", IF(COUNTIFS([Document Number]:[Document Number], [Document Number]@row) > 1, 1))
Let me know if this works for you!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Hello @Genevieve P. ,
It did not work. However I kept plugging away trying to figure it out between yesterday and today and this finally worked.
=IF(ISBLANK([Document Number]@row), 0, IF(COUNTIFS([Document Number]:[Document Number], [Document Number]@row) > 1, 1))
Thank you!
-
Hi @Joni S
Thanks for letting us know and for posting your solution!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!